unexpected search results

[DTERM]

I have the following two queries:

index=MyApp earliest=-30d@d-2h latest=-1d@d+10h | bucket _time span=24h | stats sum(eval(if((date_hour>=2) OR (date_hour<10),1,0))) as TimeWindowCount by _time |sort - _time

index=MyApp earliest=-30d@d-14h latest=-1d@d-2h | bucket _time span=24h | stats sum(eval(if((date_hour>=2) OR (date_hour<10),1,0))) as TimeWindowCount by _time |sort - _time

The objective is two have two different searches that extract data for two different time shifts for the past 30 days.

The first one extracts data for the hours of 10:00 PM through 10:00 AM (earliest=-30d@d-2h latest=-1d@d+10h) for 30 days.

The second one extracts data form the hours or 10:00 AM through 10:00 PM yesterday (earliest=-30d@d-14h latest=-1d@d-2h) for 30 days.

The problem is that the counts and data for both queries is the same. It appears to be extracting the same data. What is wrong with that syntax as listed above?

[acdevlin · Accepted solution]

If I understand your intentions correctly, then your timeframe is the issue here.

What I think you want is two different queries, one which returns events ONLY from 10am to 10pm on a given day and one which returns events ONLY from 10pm to 10am on a given day. This is not what your timeframe above is specifying however; right now, your timeframe for the first query reads "earliest=-30d@d-2h latest=-1d@d+10h". In English, this means "give me ALL events from 10pm 30 days ago to yesterday at 10am", which I don't think is what you are looking for.

Thus, the reason it looks like you're getting the same events is because you ARE getting almost identical events; your other query asks for "all events from 10am 30 days ago to 10pm yesterday", so there is a huge overlap between the search times.

In order to accomplish the search which I think you want, I would recommend the Transaction command. If you set "maxspan=12h", you should be able to get your desired results.

[acdevlin]

A fair warning about both the queries above: they will have an annoying "12:00:00.000 AM" after the date for each result.

If you want to get rid of this, do the following after the "bucket _time span=1d" section:

| eventstats count by _time | eval datestr=strftime(_time, "%Y-%m-%d") | stats values(count) by datestr

This will give you ONLY the date in year/month/day format, and you can then change the format as you see fit.

[acdevlin]

You're welcome, and sorry this has been dragged out for so long...

I think this should work for the 10am to 10pm time shift:

index=MyApp earliest=-30d@d-14h | where (date_hour>=10 AND date_hour<22) | bucket _time span=1d | stats count by _time

The other shift is a little trickier since events from 10pm-12am are on a different day from 12am-10am:

index=MyApp earliest=-30d@d-2h | where(date_hour<10 or date_hour>=22) | eval _time=if(date_hour<10, _time-86400, _time) | bucket _time span=1d | stats count by _time 

This should put all your events onto just 1 day.

[DTERM]

OK, that did not work out. Is there a way to only include a single shift in that report? For example, just 30 days of the counts of the single shift from 10 PM to 10 AM data?

For example:

1/1/11 2345
1/2/11 6567
1/3/11 5436

for the time span of 10PM to 10AM? I will have to create a second, separate query for the other shift. Thanks for your effort.

[acdevlin]

Ah. I believe I did not understand you correctly earlier...if you could be more specific in the future, it would help all of us 😉

To get the format you're after, we can use "timechart":

index=MyApp earliest=-30d@d-14h latest=-1d@d+10h | timechart span=12h count

If you are using a version of Splunk prior to 4.2, you can also specify the "fixedrange=t" option for timechart and get EXACTLY what I think you want. If not, this may or may not work properly...I am running 4.2.2 and it goes from 7am to 7pm instead.

If you are using 4.2, we'll probably need to get creative.

[DTERM]

Based on your response, I've created the following query:

index=MyApp | transaction maxspan=12h

However, this does not give me the format I need.

Ultimately, I need a report that states the number of entries in the log file between 10 am - 10 pm and 10 pm through 10 am for the past 30 days. So the report will look something like:

1/1/11 2340
1/2/11 1345
1/3/11 3876

and so one.

They can be separate reports. But how can I get that?