Search for Yesterday Total Event Minus Today's Event?

[Akmal57]

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.

[yuanliu]

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

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?

[yuanliu]

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]

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?

[yuanliu · Accepted solution]

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]

Thank You Very Much yuanliu,

its work amazing as i want,

Your query really help me.