topic Re: Timewrap: Compare last 24 hours to the same day over the last 4 weeks in Splunk Search

Timewrap: Compare last 24 hours to the same day over the last 4 weeks

RMoore01 — Tue, 05 Jun 2018 15:42:12 GMT

I would like to compare the last 24 hours to the same day the previous 4 weeks.

Re: Timewrap: Compare last 24 hours to the same day over the last 4 weeks

kmaron — Tue, 05 Jun 2018 19:50:59 GMT

when you say "last 24 hours" do you mean a specific DAY like 'Tuesday' or do you mean a rolling 24 hours (like 4pm Monday to 4pm Tuesday)?

Re: Timewrap: Compare last 24 hours to the same day over the last 4 weeks

RMoore01 — Sun, 10 Jun 2018 17:39:24 GMT

When I say "last 24 hours" I am talking about earliest=now and 24 hours from then and then the same time for the same day the previous 4 weeks.
The start time depends on when I run it, it is not a static variable.
Example:
Sunday 6/10/18 10:37 AM and the last 24 hours.
Sunday 6/03/18 10:37 AM and the last 24 hours.
Sunday 5/27/18 10:37 AM and the last 24 hours.
Sunday 5/20/18 10:37 AM and the last 24 hours.

Re: Timewrap: Compare last 24 hours to the same day over the last 4 weeks

MuS — Sun, 10 Jun 2018 18:41:14 GMT

Hi RMoore01,

I find the command multisearch really handy for this kind of comparison, also it is a very fast/efficient way to search two different time ranges. Although multisearch uses sub search syntax, it is actually not effected by any sub search limitations - from the docs http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Multisearch

With the multisearch command, the events from each subsearch are interleaved. Therefore the multisearch command is not restricted by the subsearch limitations.

Okay, now for the example; take this run everywhere example to see how it can be done :

| multisearch 
    [ search index=_internal sourcetype=splunkd earliest=-2w@-24h@h latest=-2w@-0h@h 
    | eval event="1", when="then" ] 
    [ search index=_internal sourcetype=splunkd earliest=-24h@h latest=-0h@h 
    | eval event="1", when="now" ] 
    | chart sum(event) AS events over date_hour by when

This will give you a chart with the sum of events over the last 24 hours (second search) and the same time range 2 weeks ago (First search). I had to use 2 weeks because it searches index=_internal 😉

If you add a where to the SPL, you can compare the events and show it in a graph like this:

Hope this helps ...

cheers, MuS

Re: Timewrap: Compare last 24 hours to the same day over the last 4 weeks

RMoore01 — Mon, 11 Jun 2018 13:54:59 GMT

This is very helpful, but I need the actual date and times instead of date_hour.

Re: Timewrap: Compare last 24 hours to the same day over the last 4 weeks

MuS — Mon, 11 Jun 2018 18:38:03 GMT

That will be more difficult, and comparing the results will be complicated as well....but let me try something; there are always options 😉

Re: Timewrap: Compare last 24 hours to the same day over the last 4 weeks

MuS — Tue, 12 Jun 2018 04:21:06 GMT

so, this one will give you the _time but only for the last 24 hours:

| multisearch 
    [ search index=_internal sourcetype=splunkd earliest=-2w@-24h@h latest=-2w@-0h@h 
    | eval event="1", when="then" ] 
    [ search index=_internal sourcetype=splunkd earliest=-24h@h latest=-0h@h 
    | eval event="1", when="now" ] 
| bin _time span=1h 
| fields event when date_hour 
| chart sum(event) AS events values(_time) AS _time over date_hour by when 
| where 'events: now' > 'events: then'  | table "VALUE_time: now" events* | rename "VALUE_time: now" AS _time

the events represented by then are in the same hour just two weeks earlier in this example.

cheers, MuS