Splunk Search

multiple Y axis results in same graph

zarahel
New Member

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

Tags (2)
0 Karma

zarahel
New Member

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

0 Karma

okrabbe_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

okrabbe_splunk
Splunk Employee
Splunk Employee

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

0 Karma

zarahel
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...