Converting extracted information to 12 hour AM/PM format

[harshparikhxlrd]

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:

Here is my table currently.

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

[harsmarvania57 · Accepted solution]

Hi,

Use below eval statement in your query

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

[harshparikhxlrd]

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.

[vnravikumar]

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