Splunk Search

Search for Yesterday Total Event Minus Today's Event?

Akmal57
Path Finder

Hi, 

I want to minus yesterday' total event with today's total event and divide by yesterday's total event.

To see Increase in Intrusion Events.

Please help me on query part.

Labels (7)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

Well, as long as the requirement is mathematically well define, everything is possible in SPL.  timechart in previous answers was a shortcut.  To split results by index, we'll return to stats command.

<your event search> earliest=-2d@d latest=-0d@d
| bin span=1d@d _time
| stats count values(sourcetype) as sourcetypes values(host) as hosts by index _time
| stats earliest(count) as previous_count latest(count) as yesterday_count earliest(hosts) as previous_hosts latest(hosts) as yesterday_hosts by index
| eval diff = previous_count - yesterday_count

 

View solution in original post

yuanliu
SplunkTrust
SplunkTrust
<your event search> earliest=-1d@d
| timechart span=1d@d count
| stats earliest(count) as yesterday latest(count) as today
| eval diff = today - yesterday

I am not sure how useful this calculation is, however, because you are probably search in the middle of "today", so the increase may not be reflected, if any.  Do you mean to obtain the difference between yesterday and the day before yesterday? (Both will be full 24 hours.)

Akmal57
Path Finder

Hi yuanliu,

Your query is work very well

So, if i want to see difference between yesterday and the day before yesterday i need to change 

<your event search> earliest=-1d@d

to -2d@d?

Is it possible to see the host or sourcetype in the result?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Change to

<your event search> earliest=-2d@d latest=-0d@d

Yes, you can add more info.  But depending on what exact info you want to display, the strategy can vary greatly.  Here is one clumsy example:

<your event search> earliest=-2d@d latest=-0d@d
| timechart span=1d@d count values(sourcetype) as sourcetypes values(host) as hosts
| stats earliest(count) as previous_count latest(count) as yesterday_count earliest(sourcetypes) as previous_sourcetypes latest(sourcetypes) as yesterday_sourcetypes earliest(hosts) as previous_hosts latest(hosts) as yesterday_hosts
| eval diff = yesterday_count - previous_count

 

Akmal57
Path Finder

Its work great,

last thing i want to ask,

how i want to use this query for all available index and the result will compare the event by all index.

eg field as below:

index |  yesterday count | today count | yesterday host | today host | diff

so the result will be based on index

Is it possible?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Well, as long as the requirement is mathematically well define, everything is possible in SPL.  timechart in previous answers was a shortcut.  To split results by index, we'll return to stats command.

<your event search> earliest=-2d@d latest=-0d@d
| bin span=1d@d _time
| stats count values(sourcetype) as sourcetypes values(host) as hosts by index _time
| stats earliest(count) as previous_count latest(count) as yesterday_count earliest(hosts) as previous_hosts latest(hosts) as yesterday_hosts by index
| eval diff = previous_count - yesterday_count

 

Akmal57
Path Finder

Thank You Very Much yuanliu,

its work amazing as i want,

Your query really help me. 

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 ...