Does date_hour work properly

[xvxt006]

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]

[kristian_kolb]

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

/k

[kristian_kolb]

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

/K

[xvxt006]

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.

[somesoni2 · Accepted solution]

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]

[xvxt006]

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.