topic Re: Does date_hour work properly in Splunk Search

Does date_hour work properly

xvxt006 — Mon, 28 Sep 2020 15:06:52 GMT

Hi,
I am getting order count today by hour vs last week same day by hour and having a column chart. This works fine most of the times but some times counts are wrong for the sub query. It looks like the counts are being shifted. For example, 9th hour shows 6th hour counts, etc. This does not happpen all the time but don't know why this happens some times. Any idea?

sourcetype="access_combined_wcookie" (uri=/submitOrder) earliest=-7d@d latest=-6d@d | stats count as LastWeek by date_hour | appendcols [search sourcetype="access_combined_wcookie" (uri=/submitOrder) earliest=@d latest=now | stats count as Today by date_hour]

Re: Does date_hour work properly

somesoni2 — Mon, 28 Oct 2013 23:51:43 GMT

I had this inconsistent behavior for appendcols as well. I replaced the appendcols with join and was able to get more consistent result with data in same format. Try below and let me know if it helped.

sourcetype="access_combined_wcookie" (uri=/submitOrder) earliest=-7d@d latest=-6d@d | stats count as LastWeek by date_hour | join type=outer date_hour [search sourcetype="access_combined_wcookie" (uri=/submitOrder) earliest=@d latest=now | stats count as Today by date_hour]

Re: Does date_hour work properly

kristian_kolb — Tue, 29 Oct 2013 00:25:17 GMT

It could perhaps be due to the fact that the date_* fields are extracted as-is from the events, and do not take timezones into consideration (which _time) does.

Thus you might be better off with extracting this information yourself with

| eval hr=strftime(_time, "%H")|

before the stats (in both inner and outer searches). Then use

by hr

instead of

by date_hour

Re: Does date_hour work properly

xvxt006 — Tue, 29 Oct 2013 03:52:38 GMT

Thank you. It seems to be working fine. I have to wait few days to see if i get the behavior again. Thanks for your help.

Re: Does date_hour work properly

xvxt006 — Tue, 29 Oct 2013 03:53:48 GMT

Hi Kristian, Thanks for your suggestion. I tried it and it showed the same behavior. Looks like it is due to appendcols as per below answer.

Re: Does date_hour work properly

kristian_kolb — Tue, 29 Oct 2013 07:02:49 GMT

You have read the documentation for appendcols, right?

Synopsis

Appends the fields of the subsearch results to current results, first results to first result, second to second, etc.

If there are no results for a certain time slot in either of the searches, the results would be shifted, as per documentation.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Appendcols