- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Overlay 2 time based grouped results in a Chart
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
.conf24 | Registration Open!
ICYMI - Check out the latest releases of Splunk Edge Processor
Introducing the 2024 SplunkTrust!
