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!

Announcing General Availability of Splunk Incident Intelligence!

Digital transformation is real! Across industries, companies big and small are going through rapid digital ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...