topic Re: How to format a custom time field in Splunk Search

How to format a custom time field

aaroncherian — Mon, 20 Jul 2020 19:03:27 GMT

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?

Re: How to format a custom time field

anilchaithu — Mon, 20 Jul 2020 19:36:37 GMT

@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

Re: How to format a custom time field

aaroncherian — Mon, 20 Jul 2020 19:37:02 GMT

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.

Re: How to format a custom time field

anilchaithu — Mon, 20 Jul 2020 19:41:41 GMT

@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")

Re: How to format a custom time field

aaroncherian — Mon, 20 Jul 2020 19:45:44 GMT

@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.

Re: How to format a custom time field

richgalloway — Mon, 20 Jul 2020 21:04:01 GMT

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")

Re: How to format a custom time field

aaroncherian — Mon, 20 Jul 2020 19:50:55 GMT

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

Re: How to format a custom time field

richgalloway — Mon, 20 Jul 2020 21:04:25 GMT

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

Re: How to format a custom time field

aaroncherian — Mon, 20 Jul 2020 21:08:56 GMT

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