How to format a custom time field

[aaroncherian]

Hello, 

I have a search running that shows the custom "Sign-on_Time" field in a table. I want to format it to a more readable format. 

Here is my search:

index="foo" host="bar" sourcetype="foobar" OR  sourcetype="barfoo"
| rex "Session_ID\": \"(?<Session_ID>\w+)\""
| stats values(System_Account) as System_Account values(Authentication_Type) as Authentication_Type values(Sign-on_Time) as Sign-on_Time values(Is_Admin) as Is_Admin count(eval(like(Authentication_Type,"Proxy Started"))) as SA_count values(Task) as Task by Session_ID 
| where SA_count > 0 
| where Is_Admin = 1 | table System_Account Authentication_Type Sign-on_Time Session_ID Is_Admin Task

The time comes out like this:

Is there a way for me to format it to like (HH MM SS, MM-DD-YY)?

In my Sign-on_Time field, I tried doing this:

eval signOnTime=strftime(Sign-on_Time,"%a %B %d %Y %H:%M:%S")

 and then I tried outputting that in my table and it doesn't show up.

What am I doing wrong?

[richgalloway · Accepted solution]

You have the right idea, but missed a step.  The strftime function converts an epoch (integer) into a string.  Since the Sign-on_Time field is already a string, strftime returns nothing.  The trick is to convert the string into an integer and then convert that into a string.  It can be done in a single eval.

eval Sign-on_Time=strftime(strptime('Sign-on_Time',"%Y-%m-%dT%H:%M:%S"),"%a %B %d %Y %H:%M:%S")

[aaroncherian]

Hi @richgalloway 

I tried this, and I am getting the same error as when I tried @anilchaithu 's solution.

Error in 'eval' command: The arguments to the 'strptime' function are invalid

[richgalloway]

Try putting single quotes around the field name as in my updated reply.

[aaroncherian]

Worked Like a charm! Thank you @richgalloway and @anilchaithu for your help!

[anilchaithu]

@aaroncherian 

try this

index="foo" host="bar" sourcetype="foobar" OR  sourcetype="barfoo"
| rex "Session_ID\": \"(?<Session_ID>\w+)\"" | eval signOnTime=strptime(Sign-on_Time,"%Y-%m-%dT%H:%M:%S-07:00")| eval Sign-on_Time=strftime(signOnTime,"%m-%d-%Y %H:%M:%S")
| stats values(System_Account) as System_Account values(Authentication_Type) as Authentication_Type values(Sign-on_Time) as Sign-on_Time values(Is_Admin) as Is_Admin count(eval(like(Authentication_Type,"Proxy Started"))) as SA_count values(Task) as Task by Session_ID 
| where SA_count > 0 
| where Is_Admin = 1 | table System_Account Authentication_Type Sign-on_Time Session_ID Is_Admin Task

Please refer: https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/DateandTimeFunctions

Hope this helps

[aaroncherian]

Hi @anilchaithu 

Thanks for the reply, but I tried this, and it keeps saying 

Error in 'eval' command: The arguments to the 'strptime' function are invalid. 

This was the same error I was getting before as well.

[anilchaithu]

@aaroncherian 

one more try. Hope this works. fingers crossed 🙂

| eval signOnTime=strptime(Sign-on_Time,"%Y-%m-%dT%H:%M:%S%z")| eval Sign-on_Time=strftime(signOnTime,"%m-%d-%Y %H:%M:%S")

[aaroncherian]

@anilchaithu 

I tried that, it doesn't work as well 😞 Something wrong with the formatting. I cant quite figure out what. Your strptime seems correct.