Splunk Search

How to format a custom time field

aaroncherian
Path Finder

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:

aaroncherian_0-1595270650463.png

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?

Labels (4)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.

aaroncherian
Path Finder

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
0 Karma

richgalloway
SplunkTrust
SplunkTrust
Try putting single quotes around the field name as in my updated reply.
---
If this reply helps you, Karma would be appreciated.

aaroncherian
Path Finder

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

0 Karma

anilchaithu
Builder

@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
Path Finder

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.

0 Karma

anilchaithu
Builder

@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
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...