How to set _time to specific limit(with in days) in earliest 30 days logs

[kumarrm]

Splunk Query:

"JDW14563" "START TIME" earliest=-30d | eval seconds=(date_hour*360)+(date_minutes*60)| chart values latest(_time) AS LAST_START_TIME,median(seconds) AS AVG_START_TIME by JOBNAME

This query fetches values for 30days and provide average start time and latest time stamp of the JDW14563 as LAST_START_TIME in 30days.

I want to calculate the average and need to fetch the latest time stamp for the a particular day in 30days (for example in June month i want to know the latest time stamp value between 6/18/2016:00:00:00 to 6/18/2016:23:00:00) and this _times should have have the values of specific days alone instead of all 30 days

Could some one help me please...!

[sundareshr]

Are you looking for earliest, not latest? Try this

"JDW14563" "START TIME" earliest=-30d | eval seconds=(date_hour*360)+(date_minutes*60)| chart values earliest(_time) AS LAST_START_TIME,median(seconds) AS AVG_START_TIME by JOBNAME

[Richfez · Accepted solution]

To get the latest time stamp value for each day, you can use some of the other stats functions, namely latest. I do it in a timechart because I feel the examples end up being more clear, and in this case far easier. There are a few tricks to this because you can't use _time directly.

"JDW14563" "START TIME" earliest=-30d
| eval myTime=_time
| timechart span=1d count, latest(myTime) as latest_time 
| fieldformat latest_time=strftime('latest_time', "%c")

Line 1 is just your search.
Line 2 creates a copy of each _time into a new field. You can't use _time because, for reasons I'm sure one of the smarter folks on here can enumerate, it would be wrong. (Try it and see if you want).
Line 3 is the magic, where we now re-partition our 30 days into 1 day chunks and take the latest of each myTime as a new field. You can add more calculations to this if you need more fields.
Line 4 just makes the latest_time show up as a real date/time instead of a Unix Epoch. There are other formats.

[kumarrm]

Thanks rich... It's works to find the date...

[kumarrm]

Thanks Rich for the reply.

I have to check in my work area , will have a validation and update you. I would like to know

the below part will help me to find the particular day logs in 30days log ? For example in 30 days will I able to get logs between 6/23/2016:15:00:00 to 6/24/2016 07:00:00 ? Please help me to understand.

|timechart span=1d count, latest(myTime) as latest_time

[Richfez]

Hmm, I'm not sure what exactly you need any more, though the several various scenarios I can think of are all solvable so please keep asking! Two of the possibilities are below, more could be forthcoming if you need them.

First, in case we're overlooking something obvious - you know the time-picker has other options than only "previous 30 days" or something, right? You can pick date ranges or date and time ranges. You can also start clicking and dragging and zooming in and out on your time selector bar (the green counter bar thingy). I believe the Splunk Tutorial (free e-learning) can help you with this.

But I expect that's not what you need.

If instead you want to only "retrieve" certain sets of hours ? In that case, there's a slightly different method.

"JDW14563" "START TIME" earliest=-30d
| eval hour = tonumber(strftime(_time,"%H"))
| eval dom = tonumber(strftime(_time,"%d"))
| where (hour>=15 AND dom=21) OR (dom=22) OR (hour<=7 AND dom=23)

So what are we doing there? You are familiar with the first line already.
The second and third lines create a field called hour and dom (day of month) which uses the function tunumber and strftime to extract the hour and day of month. It's better this way than using the built-in "date_hour" and whatnot because those aren't always locale or daylight savings aware or something.
Then, though the logic can get a bit on the dipsy side, we filter our results down farther. In this case, where (hour>=15 AND dom=21) OR (dom=22) OR (hour<=7 AND dom=23) will get you anything that happened after 3 PM on the 21st, anything on the 22nd, and anything before 7 AM on the 23rd.

If that suits what you need, great. If it doesn't, could I ask you to spend a few minutes to come up with a) a better description of what and especially WHY you are doing what you are doing, b) what you want your report/search's results to actually be like and also c) a more complete example? Those would go far to answer the questions rolling around in my head and really help us give you the answer you need.