Hello to you all
I need your help
I´m performing a MySQL query and getting the following results:
TIME || RESULTS || URL
2013-06-23 || 22 || www.youtube.com
2013-06-23 || 22 || www.youtube.com
2013-06-23 || 22 || www.google.com
2013-06-23 || 21 || www.google.com
2013-06-23 || 23 || www.facebook.com
2013-06-23 || 21 || www.facebook.com
I´m trying to create a graph where the X axis is the time, and the Y axis is composed by 3 lines, where each line corresponds to the results obtained for each of the 3 URLs (facebook, google and youtube).
I´m not sure how to do it. I´ve read some examples where some tried to overlap different searches or different fields, but my case is a little different, since I need to create 3 lines based on different values from the same field 'URL', performed by 1 search.
Any help is appreciated
Thank you
Ok, so I figured it out. The problem was in Splunk being able to interpret my Mysql DB time format. In my Mysql query I converted it to timestamp and named it AS _time (as used by Splunk). After that, I just had to manipulate the timechart command.
My final, and working search:
| mysqlquery spec=database query="SELECT unix_timestamp(time) AS _time , url , AVG(results) as AVGRES FROM result_tbl BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 1 DAY ) AND CURDATE( ) GROUP BY url, DATE(time), HOUR(time);" schema=database | timechart span=1h avg(AVGRES) by url
Thank you for your help
You can use the timechart command:
your_search | timechart count by URL
You may need to format it so that that it shows bars rather than lines.
Please let me know if that helps.
Ok, now I understand a bit better. You cannot use timechart because your time variables are not stored in our _time variable. You do need to convert it to epoch time.
|mysqlquery "myquery" | eval _time=strptime(time,"%Y-%m-%d-%H") | timechart count by url
If this does not work , can you paste the out of the following:
|mysqlquery "myquery" | eval _time=strptime(time,"%Y-%m-%d-%H") | table _time time url
okrabbe_splunk
Thank you for your feedback. It didn´t work.
I´m using MySQLConnector to perform the query, and when I add the timechart command, I get "no results found"
This is the query I´m running:
| mysqlquery spec=database query="SELECT time AS ATIME , url , AVG(results) FROM result_tbl BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 1 DAY ) AND CURDATE( ) GROUP BY url, DATE(time), HOUR(time);" schema=database | timechart count by url
If I remove the timechart command I get the results