How to get event count from current hour and previous hour by sourcetype and server/host

[catherineang]

The goal is to compare the events from this hour vs the past hour. And then display a table by sourcetype, host, percent, difference, current count, previous hour count.

This is my query:

index=x sourcetype=* host=* earliest=-2h@h latest=now | eval period=if(_time>=relative_time(now(),"-1hr"),"current","previous") | chart count(sourcetype) over host by period | eval difference=current-previous | eval percent=(current/previous)*100| table sourcetype host percent difference current previous

The problem is, sourcetype column is blank and host column and count appears. It doesn't count by sourcetype and host. If I do "chart count(host) over sourcetype by period", only the host column would be blank and sourcetype will show and count on the table.
Example:

HOST SOURCETYPE PERCENT DIFFERENCE CURRENT PREVIOUS
x 100 0 1 1
Y 100 0 1 1
Z 100 0 1 1

Should be something like this:

HOST SOURCETYPE PERCENT DIFFERENCE CURRENT PREVIOUS
x A 100 0 1 1
Y B 100 0 1 1
Z A 100 0 1 1

[kamlesh_vaghela · Accepted solution]

@catherineang

Can you please try this?

index=x sourcetype=* host=* earliest=-2h@h latest=now 
| eval period=if(_time>=relative_time(now(),"-1hr"),"current","previous") 
| eval temp=host.",".sourcetype
| chart count over temp by period 
| eval difference=current-previous 
| eval percent=(current/previous)*100 | eval host=mvindex(split(temp,","),0),sourcetype=mvindex(split(temp,","),0) 
| table sourcetype host percent difference current previous

[catherineang]

I updated the sourcetype index to 1 and it works like a charm! Thanks @kamlesh_vaghela !

[kamlesh_vaghela]

@catherineang

Glad to help you.

Happy Splunking

[jabezds]

I want to display the above details in splunk.. 33.5k is the total count of events(_raw).Here i am comparing the events from previous  24 hour ,with the latest 24 hour.....with 1% being the average and inverted triangle can be + or - ve value obtained from the average.

Can Someone help with this?

Thanks in Advance!

[catherineang]

I updated the sourcetype index to 1 and it worked like a charm! Thank you @kamlesh_vaghela !