I am running a report that outputs a date and time format form one of my logs, and sending it in email to a customer. they cannot create a pivot table off the time as excel does not recognize the format. I am attemtping to change the date/time stamp field to a new value (logintime) with the new date format.
Any help would be greatly appreciated.
index=indexname Field=Value | eval logintime = strptime(timefield, "%a %b %d:%H:%M:%S %Z %Y") | eval desired_time=strftime(logintime, "%d/%m/%Y %I:%M:%S %p") | fields - logintime | stats count by UserData_LastName logintime
Current time format=Thu Nov 06 11:03:18 EST 2014
Which part of your search isn't working? Creating a new time field? Or just reporting it?
The first thing I would do would be to change the stats
portion of your search. You are getting rid of the logintime field with the fields
command, so when you reference it later in your stats
command, there won't be anything there.
Is timefield in your first eval
is the same thing as the event's time (i.e. _time)? If so, you could do something like this:
index=indexname Field=Value | eval desired_time=strftime(_time, "%d/%m/%Y %I:%M:%S %p") | stats count by UserData_LastName desired_time
I think all you need to do now is change your stats command to use desired_time instead of logintime. Your are eval'ing logintime to epoch format, then from it creating a new field in human readable format called desired_time, but you're still referencing the epoch time field in stats.
Thank you both! that did the trick
Which part of your search isn't working? Creating a new time field? Or just reporting it?
The first thing I would do would be to change the stats
portion of your search. You are getting rid of the logintime field with the fields
command, so when you reference it later in your stats
command, there won't be anything there.
Is timefield in your first eval
is the same thing as the event's time (i.e. _time)? If so, you could do something like this:
index=indexname Field=Value | eval desired_time=strftime(_time, "%d/%m/%Y %I:%M:%S %p") | stats count by UserData_LastName desired_time
The strptime
format string has an extra colon in it. Try '%a %b %d %H:%M:%S %Z %Y'. Also, the stats
command refers to logintime
which was dropped by the preceding fields
command.
Thanks Rich,
My output seems to show up in epoch time.
Time format after execution1415328453.000000
eval logintime = strptime(responseIssueInstant, "%a %b %d %H:%M:%S %Z %Y") | eval desired_time=strftime(logintime, "%d/%m/%Y %I:%M:%S %p") | stats count by UserData_LastName logintime
pete_charlton gravatar image
Answer by pete_charlton
2 secs ago
Add comment · Accept
The stats
command is displaying logintime
, which is epoch time (the output of strptime()). To see the desired time string, put desired_time
in the stats
command.