topic Re: How to get event count from current hour and previous hour by sourcetype and server/host in Splunk Search

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

catherineang — Thu, 04 Oct 2018 04:11:00 GMT

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

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

kamlesh_vaghela — Thu, 04 Oct 2018 14:16:59 GMT

@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

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

catherineang — Tue, 09 Oct 2018 02:05:06 GMT

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

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

catherineang — Tue, 09 Oct 2018 02:06:35 GMT

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

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

kamlesh_vaghela — Mon, 11 Feb 2019 05:20:53 GMT

@catherineang

Glad to help you.

Happy Splunking

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

jabezds — Mon, 06 Jul 2020 15:01:58 GMT

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!