Splunk Search

Overlay 2 time based grouped results in a Chart

GadgetGeek
Path Finder

Given the 2 following searches which are both over a 30 day period (and each having multiple countries in the results) how do I:

  • Show the time as day range (0-30) instead of the actual dates showing
  • Then overlay the 2 search results into the same line chart (as they are both over a 30 day period)

The searches are:

<query> earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | bin _time span=1d | stats count as DailyTotal by Country _time| timechart avg(DailyTotal) as AvgPerDay by Country

And

<query> earliest=-30d@d latest=now  | rex "country=(?<Country>[a-zA-Z]*)\s" | bin _time span=1d | stats count as DailyTotal by Country _time| timechart avg(DailyTotal) as AvgPerDay by Country

After hours of searching - this result produces overlaid lines BUT NOT with the 'Country' grouping and the chart time period shows 60 days not 30...

earliest=-60d@d latest=now  | rex "country=(?<Country>[a-zA-Z]*)\s" | bin _time span=1d | stats count as DailyTotal by Country _time| eval marker=if(_time<relative_time(now(), "-30d@d"), "Last Month", "This Month") | eval _time=if(marker=="Last Month", _time+(60*60*24*30), _time) |  timechart avg(DailyTotal) as AvgPerDay by marker

How can I get the grouping back?

Tags (3)
0 Karma
1 Solution

jeffland
SplunkTrust
SplunkTrust

First of all, you're doing unnecessary oparations - after bucketing by day and then counting by day, averaging by day is futile. So you could change your first search to the following and it should do the same, only quicker:

earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | timechart span=1d count as CountPerDay by Country

Now, on to your original problem. To show a day instead of a date, you need to eval _time with strftime. In your case, that would yield something like

earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | eval day=strftime(_time, "%d") | chart avg(DailyTotal) as AvgPerDay by day Country

for the first search.

To overlay two different time ranges in one timechart, you generally need to eval the _time field of one of the searches to the same period as the other one (so 30 days ahead in your case). See this blog post for details. The problem is, you will need to do this before you count by your new day field because that one can't be used to go back 30 days. So your searches could end up something like the following:

earliest=-30d@d latest=now | rex "country=(?<Country>[a-zA-Z]*)\s" | eval key="this month" | eval day=strftime(_time, "%d") | append [search earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | eval key="last month" | eval _time=_time+2592000 | eval day=strftime(_time, "%d")] | eval key=Country." ".key | chart count by day key

I've had to change the calculation of _time to an external field sometimes, so if the above doesn't do the trick this one might:

earliest=-30d@d latest=now | rex "country=(?<Country>[a-zA-Z]*)\s" | eval key="this month" | eval day=strftime(_time, "%d") | append [search earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | eval key="last month" | eval time=_time+2592000 | eval day=strftime(_time, "%d")] | eval _time=if(key="last month",time,_time) | eval key=Country." ".key | chart count by day key

See how far you get and feel free to ask questions if something doesn't work out.
PS: I've edited some of the search in this answer, it should now work.

View solution in original post

jeffland
SplunkTrust
SplunkTrust

First of all, you're doing unnecessary oparations - after bucketing by day and then counting by day, averaging by day is futile. So you could change your first search to the following and it should do the same, only quicker:

earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | timechart span=1d count as CountPerDay by Country

Now, on to your original problem. To show a day instead of a date, you need to eval _time with strftime. In your case, that would yield something like

earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | eval day=strftime(_time, "%d") | chart avg(DailyTotal) as AvgPerDay by day Country

for the first search.

To overlay two different time ranges in one timechart, you generally need to eval the _time field of one of the searches to the same period as the other one (so 30 days ahead in your case). See this blog post for details. The problem is, you will need to do this before you count by your new day field because that one can't be used to go back 30 days. So your searches could end up something like the following:

earliest=-30d@d latest=now | rex "country=(?<Country>[a-zA-Z]*)\s" | eval key="this month" | eval day=strftime(_time, "%d") | append [search earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | eval key="last month" | eval _time=_time+2592000 | eval day=strftime(_time, "%d")] | eval key=Country." ".key | chart count by day key

I've had to change the calculation of _time to an external field sometimes, so if the above doesn't do the trick this one might:

earliest=-30d@d latest=now | rex "country=(?<Country>[a-zA-Z]*)\s" | eval key="this month" | eval day=strftime(_time, "%d") | append [search earliest=-60d@d latest=-30d@d  | rex "country=(?<Country>[a-zA-Z]*)\s" | eval key="last month" | eval time=_time+2592000 | eval day=strftime(_time, "%d")] | eval _time=if(key="last month",time,_time) | eval key=Country." ".key | chart count by day key

See how far you get and feel free to ask questions if something doesn't work out.
PS: I've edited some of the search in this answer, it should now work.

GadgetGeek
Path Finder

Thanks for getting me there in the end - and I learned a lot along the way!!

brettcave
Builder

If you want to optimize your search (instead of using an append), you could do a search for -60d - now and then do evals based on whether time is more than 30 days or not (case or if).

0 Karma

brettcave
Builder

Something like this:
earliest="-60d@d" latest=now ....
| eval key=if(_time > relative_time(now(), "-30d@h"),"currentPeriod","previousPeriod") | eval _time=if(key="previousPeriod",_time+604800,_time) | eval key=Country." ".key | timechart ..

Not using chart with day, but timechart with the _time field.

0 Karma

GadgetGeek
Path Finder

Your help is most appreciated! (Even when it doesn't work 😉 )

0 Karma

jeffland
SplunkTrust
SplunkTrust

Ok, please try again.

0 Karma

GadgetGeek
Path Finder

I think that's it - just off to check the numbers.....

0 Karma

GadgetGeek
Path Finder

Oh, spotted that you use 'if (key="previous month"... but the search uses "last month"

jeffland
SplunkTrust
SplunkTrust

I corrected that in the answer, thanks for pointing it out.

0 Karma

GadgetGeek
Path Finder

Adding the search criteria back into the 2nd search (after append) helps! Better numbers coming out now...

0 Karma

GadgetGeek
Path Finder

Yes it works - with a caveat...

Is there any way to get it to have the numbering on the time axis being from -30 to 0?

0 Karma

jeffland
SplunkTrust
SplunkTrust

Definitely. I assume you want -30 instead of 0 and 0 instead of 30, then simply add an eval for that somewhere:

... | eval day=day-30
0 Karma

GadgetGeek
Path Finder

You're right on the first part - counting was what I should have done!

But I get some strange results using both the last answers! I was expecting 2 lines in the chart per country (one for the last 30 days, one for the previous 30 days to that). I'm left with:

3 lines: key, Country, count - none of which make sense - neither do the axes

I'm too new at this to get to grips with it, but this seems further away than at the start?

0 Karma

jeffland
SplunkTrust
SplunkTrust

Sorry, there was a stats instead of a chart in the final version. Please try again.

0 Karma

GadgetGeek
Path Finder

Now I get:

Error in 'chart' command: The argument 'Country' is invalid.

😞

0 Karma

jeffland
SplunkTrust
SplunkTrust

Damn, yeah I just noticed this can't work - chart only accepts two split-by arguments (there are only two axes). Lemme work some more on that.

0 Karma

woodcock
Esteemed Legend

You need the timewrap app, for sure:

https://splunkbase.splunk.com/app/1645/

0 Karma

GadgetGeek
Path Finder

We can't use any additional plugins...

0 Karma

woodcock
Esteemed Legend

Downloading the app is no longer necessary; later versions of Splunk have this command built-in!

0 Karma

woodcock
Esteemed Legend

Download the app and clone the guts manually.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...