Splunk Search

Converting extracted information to 12 hour AM/PM format

Path Finder

Hello, I am extract information from logs via rex, and I am currently extra information in military time format. (i.e.: 13:15). I also extract things such as 11:15, but I want it to be consistent in a 12 hour AM/PM format. Example: 1:15 PM instead of 13:15. 11:15 AM instead of 11:15. I was wondering if it were possible to convert the information I extract, if it is between 13:00 and 23:59, that would be PM.

Here is my log:
alt text
Here is my table currently.
alt text

Here is my query so far:

index=monitoring sourcetype=PEGA:WinEventLog:Application ( SourceName="RoboticLogging" OR SourceName="Application" )  ("Type=" "Information") 
| rex field=_raw "Department=\"(?<Department>.+?)\"" 
| where Department = "HRSS_NEO" OR Department = "HRSS Daily NEO Report"
| rex "Duration:\s*(?<hh>\d+):(?<mm>\d+):(?<ss>\d+\.\d+)" 
| rex "Number of supervisor reminder memos sent:\s*(?<memo>[^,]+)"
| rex "Number of New Employees in NEO Report with job title Temporary Agy Svc Asst:\s*(?<yes>[^,]+)"
| rex "Number of New Employees in NEO Report without job title Temporary Agy Svc Asst:\s*(?<no>[^,]+)"
| rex "Number of supervisors found when searching AD:\s*(?<valid>[^,]+)"
| rex "UserID=\"UNTOPR\\\(?<UID>.+?)\""  
| rex "Number of supervisors not found when searching AD:(?<invalid>[^,]+)"
| rex "Email Received\s*Time:(?<received>.{5}?)"
| rex "Email Process Started At:\s*(?<processed>.{5}?)"
| eval processed = if(isnull(processed), "-", processed)
| rex "StartTime:\s*(?<startTime>.{5})"
| eval startTime = if(isnull(startTime), "-", startTime)
| eval dur = round(((hh * 3600) + (mm * 60) + ss),0) 
| eval avghndl = round(dur/memo, 0)
| eval dur = tostring(dur,"duration")
| eval avghndl = tostring(avghndl,"duration")
| eval Time = strftime(_time, "%m/%d/%Y at %r")
| where dur != " " 
| eval valid = if(isnull(valid), "0", valid) 
| eval received = if(isnull(received), "-", received)
| replace "" with "0"
| eval strr = host." : ".UID  
| eval strr=upper(strr)
| eval invalid = if(isnull(invalid), "0", invalid) 
| fields - _time 
| dedup Time 
| table strr, Time, dur, received, startTime, processed, memo, yes, no, valid, invalid, avghndl,  
| rename strr as "Workstation : User", dur as "Duration (HR:MIN:SEC)", memo as "Supervisor Reminder Memos Sent", yes as "New Temporary Employees", no as "New Employees (Not Temporary)", valid as "Valid Aliases", invalid as "Invalid Aliases", avghndl as "Average Handle Time per Email", received as "Email Received Time", startTime as "Start Time", processed as "Email Processed Time"
| sort by Time desc
0 Karma
1 Solution

SplunkTrust
SplunkTrust

Hi,

Use below eval statement in your query

| eval startTime=strftime(strptime(startTime, "%H:%M"), "%I:%M %p")

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Hi,

Use below eval statement in your query

| eval startTime=strftime(strptime(startTime, "%H:%M"), "%I:%M %p")

View solution in original post

0 Karma

Path Finder

Whoa, that worked. I just had to change the "Start Time" to startTime to match my query, but this is perfect and simple. Thank you!! I never thought to try a strptime within a strftime.

0 Karma

Champion

Hi

Try this

| makeresults 
| eval time= "13:15,11:15" 
| makemv delim="," time 
| mvexpand time 
| eval temp =strftime(strptime(time,"%H:%M"),"%I:%M %p")
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!