How to get the total count of events for earliest hour and latest hour time for a particular field?

[vrmandadi]

I am trying to get the total count of a field called ID for earliest and latest time for a particular time range. Assume I am looking for a time range of Mor 8AM to 5PM . I want the count of total for a field called "ID" for 8AM TO 9AM and also count from 4PM TO 5PM for field called 'ID" and show what is different if there is a difference in values of ID for hours 8AM TO 9AM and 4PM TO 5PM .

Following is the query I am using

index=test
| rename "results{}.id" as "id"
| bin _time span=1h | stats count(id) as total by _time
| delta total as difference
| fillnull value=0
|eval status=case(difference=0, "No change", difference<0, "Device(s) Removed" , difference>0 ,"Device(s) Added")
| search status!="No change"
| rename _time as time | eval time=strftime(time,"%m/%d/%y %H:%M:%S")

[ITWhisperer · Accepted solution]

Try something like this

index=test (earliest=@d+8h latest=@d+9h) OR (earliest=@d+16h latest=@d+17h)
| rename "results{}.id" as "id"
| bin _time span=1h | stats count(id) as total by _time
| delta total as difference
| fillnull value=0
|eval status=case(difference=0, "No change", difference<0, "Device(s) Removed" , difference>0 ,"Device(s) Added")
| search status!="No change"
| rename _time as time | eval time=strftime(time,"%m/%d/%y %H:%M:%S")

[vrmandadi]

Thank you it worked.I never know that we can add + in earliest and latest.Thank you I learned something new

[vrmandadi]

I tried to run line by line. I ran the below search but I only got 8AM events.

index=test (earliest=@d+8h latest=@d+9h) OR (earliest=@d+16h latest=@d+17h)
| rename "results{}.id" as "id"
| bin _time span=1h | stats count(id) as total by _time

[ITWhisperer]

What time did you run it at? The time selection is based on today - if you want yesterday for example use

index=test (earliest=-1d@d+8h latest=-1d@d+9h) OR (earliest=-1d@d+16h latest=-1d@d+17h)
| rename "results{}.id" as "id"
| bin _time span=1h | stats count(id) as total by _time