Why is my search showing 2 values for the same hour?

[xvxt006]

I am using the search below to compare this week vs last week same hour counts, but in the results, for some of the hours, I am seeing 2 values for the same hour as shown in the screenshot

What could be the reason for this?

 status=404  | stats count as Today by date_hour | appendcols [search sourcetype=access_combined_wcookie status=404 earliest=-7d@d latest=-6d@d | stats count as LastWeek by date_hour] | sort date_hour

[MuS · Accepted solution]

Hi xvxt006,

I cannot tell you why this happens, but I can show you a more efficient search to achieve the same result. Take this run everywhere example:

index=_internal sourcetype=splunk_web_access status=200 earliest=-14d@d latest=now | eval last_week=relative_time(now(), "-7d@d") | stats count(eval(if(last_week > _time, status, null()))) AS last_week count(eval(if(last_week < _time, status, null()))) AS this_week by date_hour 

It will only run one search and count's based on the time of the events.

Hope this helps to sort this out ...

cheers, MuS

[xvxt006]

:-)...I missed that. Thank you.

[MuS]

you're welcome 🙂

[xvxt006]

One last question - by using the above query, for "this week" we are going to get the counts for whole week right? What if i just want to get the count only for today compared to same day last week by date_hour?

[xvxt006]

Nice. i have tried that..

status=404  earliest=@d latest=now | eval last_week=relative_time(now(), "-7d@d") | stats count(eval(if(last_week > _time, status, null()))) AS last_week count(eval(if(last_week < _time, status, null()))) AS this_week by date_hour | table date_hour, last_week, this_week

For some reason it shows 0 counts for the last week

[MuS]

the base search used earliest=@d and later you used in the eval -7d@d so you will never get any data for the last_week 🙂

Try this for yesterday and today comparison:

status=404  earliest=-1d@d latest=now | eval last_day=relative_time(now(), "-0d@d") | stats count(eval(if(last_day > _time, status, null()))) AS yesterday count(eval(if(last_day < _time, status, null()))) AS today by date_hour