Splunk Search

How do I compare search results from two different time periods?

Explorer

Good day!

Could you help me, please?

I need to compare the number of unique user connections in two time intervals: 1) for all time; 2) for all time without the last hour.
Now my search query looks like this, but it does not work correctly:

sourcetype = * earliest = 1 latest = now()
| stats count(DeviceID) as DeviceID_ALL by Login
| appendcols
     [sourcetype = * earliest = 1 latest = -1h@h | stats count(DeviceID) as DeviceID_withoutlasthour by Login]
| table Login, DeviceID_ALL, DeviceID_withoutlasthour

If you can help me, I would be overly grateful.
Thanks in advance!

0 Karma
1 Solution

Esteemed Legend

Like this:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo earliest=0 latest=now()
| eval which=if(_time>relative_time(now(), "-1h@h"), mvappend("DeviceID_all", "DeviceID_lasthour"), "DeviceID_all")
| chart count(DeviceID) by Login which
| eval DeviceID_withoutlasthour = DeviceID_all - DeviceID_lasthour
| fields - DeviceID_lasthour

View solution in original post

Esteemed Legend

Like this:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo earliest=0 latest=now()
| eval which=if(_time>relative_time(now(), "-1h@h"), mvappend("DeviceID_all", "DeviceID_lasthour"), "DeviceID_all")
| chart count(DeviceID) by Login which
| eval DeviceID_withoutlasthour = DeviceID_all - DeviceID_lasthour
| fields - DeviceID_lasthour

View solution in original post

Path Finder

Hi,

 earliest=1 sourcetype=*  latest=now()
| stats count(DeviceID) as DeviceID_ALL by Login
| appendcols
[ search earliest=1 sourcetype=*  latest=-1h@h | stats count(DeviceID) as DeviceID_withoutlasthour by Login]
| table Login, DeviceID_ALL, DeviceID_withoutlasthour

Choose your sourcetype - it was not equaling anything in original query.

0 Karma