Hi, I was hoping for help on this. I want to reformat a date as follows (and if there is an easier--more condense way--I'm all ears):
With 'date_month'
being the full month name, I want a final date of the form, for example: 2013-10-18-15.00.00
BUT, I always end up with the hour being: 00, for example: 2013-10-18-00.00.00
I am using:
index . . . | stats sum(YYY) by date_year date_month date_mday date_hour | eval datestr=date_year."-".date_month."-".date_mday."-".date_hour
| eval datestr2 = strftime( strptime( datestr, "%Y-%B-%d-%H" ), "%Y-%m-%d-%H.00.00") | table datestr2 sum(YYY)
I can see datestr is: 2013-october-18-15 so it pulled the hour correctly.
I can see strptime is in 'epoch time' and maybe that is the reason, it is: 1382054400.000000
Thanks.
You're right by the way - your time is being extracted from an epochtime value and there's a longstanding bug in Splunk where it cannot determine timezone when it gets the time from an epochtime value. Instead of using local server time, or failing that not creating date_*
fields at all, splunkd creates all the date_*
fields as though the data was from the GMT timezone. So effectively date_*
fields is always unreliable whenever _time is extracted from epochtime values. (unless you happen to use GMT on that host. 😃
Thanks to both somesoni2 and aelliott, both solutions work - and am glad one of them is 'condensed' so to speak. (FYI: I tried to award the maximum number of points for each but it looks like the field will only take 1 digit - so I put in a '9').
does something like this not work?
index=* | eval Date=strftime(_time, "%m/%d/%Y-%H.00.00") | stats count(host) by Date
This should do the trick for you
index . . . | stats sum(YYY) by date_year date_month date_mday date_hour
| eval datestr=relative_time(strptime(date_year."-".date_month."-".date_mday , "%Y-%B-%d" ),"+".date_hour."h@h")
| eval datestr2 = strftime(datestr, "%Y-%m-%d-%H.00.00")
| table datestr2 sum(YYY)
Thank you, but for what I put, you say it works? It does NOT have '00' for the hour, that is, it does NOT have: 2013-10-18-00.00.00 ? It has: 2013-10-18-15.00.00 ?
I should have added that I had already tried: strftime( _time, "%Y-%m-%d-%H.00.00") but I do not get anything back for the date:
index= . . . | stats sum(nrcpts) by date_hour date_mday
| eval Date=strftime(_time, "%m/%d/%Y-%H.00.00") | table Date sum(nrcpts)
That works for me what you have above,
If you are looking to use the timestamp on the event, you could use
index= . . .| eval Date=strftime(_time, "%m/%d/%Y-%H.00.00") | stats count(host) by Date
or ... | timechart span=1h sum(YYY)
/k