topic Re: multiple Y axis results in same graph in Splunk Search

multiple Y axis results in same graph

zarahel — Mon, 24 Jun 2013 20:02:52 GMT

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.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

Re: multiple Y axis results in same graph

okrabbe_splunk — Mon, 24 Jun 2013 20:36:45 GMT

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.

Re: multiple Y axis results in same graph

zarahel — Mon, 28 Sep 2020 14:10:07 GMT

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

Re: multiple Y axis results in same graph

okrabbe_splunk — Tue, 25 Jun 2013 13:05:28 GMT

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

Re: multiple Y axis results in same graph

zarahel — Mon, 28 Sep 2020 14:10:10 GMT

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