Splunk Enterprise

Trying to get the correct format for start and end time

chinmay25
Path Finder

I am working with the following query....however, the start time and end tied output that i am getting is below and some of the times is listed several times.

06/22/2020 15:24:06.370000

I am trying to get only the time instead of the current format. 

 

index= XYZ  SMF30JBN=M*DDD* SMF30JNM=JOB* (SMF30STP=1 OR SMF30STP=5) sourcetype="syncsort:smf030"
| rename SMF30JNM as JOBNUMBER SMF30JBN as JOBNAME
| eval START = case(SMF30STP=1,strptime(DATETIME, "%Y-%m-%d %H:%M:%S.%2N"))
| eval END = case(SMF30STP=5,strptime(DATETIME, "%Y-%m-%d %H:%M:%S.%2N"))
| stats values(START) as START values(END) as END by JOBNUMBER JOBNAME
| convert dur2sec(START) as STARTTIME dur2sec(END) as ENDTIME
| convert ctime(STARTTIME) as START_TIME ctime(ENDTIME) as END_TIME
| table JOBNAME START_TIME END_TIME

Labels (1)
0 Karma
1 Solution

anilchaithu
Builder

@chinmay25 

one solution would be using the below command instead of convert

| eval START_TIME=strftime(START , "%H:%M:%S),  END_TIME=strftime(END , "%H:%M:%S)

| table JOBNAME START_TIME END_TIME

The multiple values are due to the below command

| stats values(START) as START values(END) as END by JOBNUMBER JOBNAME



 

View solution in original post

anilchaithu
Builder

@chinmay25 

one solution would be using the below command instead of convert

| eval START_TIME=strftime(START , "%H:%M:%S),  END_TIME=strftime(END , "%H:%M:%S)

| table JOBNAME START_TIME END_TIME

The multiple values are due to the below command

| stats values(START) as START values(END) as END by JOBNUMBER JOBNAME



 

chinmay25
Path Finder

Hi Anil,

 

Thank you for the help. It gave the desired output for the time format.

However, if i remove the stats command to remove the duplicates of time... most of my data is missing with either start time or end time.

start timeend time
6:55:53 
6:55:33 
 6:55:32
6:54:49 
 6:54:48

 

Where as if i keep the stats command I see the result where some of the time is showing multiple events.

ex. 

8:40:42
8:40:42

 

Tags (1)
0 Karma

anilchaithu
Builder

@chinmay25 

you can use mvdedup after stats to remove duplicate values.

eval START_TIME=mvdedup(START_TIME), END_TIME=mvdedup(END_TIME)

also you can use list in place of values

| stats list(START) as START list(END) as END by JOBNUMBER JOBNAME

 

If this helps, upvote would be appreciated

 

 

0 Karma

chinmay25
Path Finder

Hi Anil,

The latest suggestion didnt help. But thanks for the earlier helped and I will vote.

 

Chinmay.

0 Karma
Get Updates on the Splunk Community!

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...

[Live Demo] Watch SOC transformation in action with the reimagined Splunk Enterprise ...

Overwhelmed SOC? Splunk ES Has Your Back Tool sprawl, alert fatigue, and endless context switching are making ...

What’s New & Next in Splunk SOAR

Security teams today are dealing with more alerts, more tools, and more pressure than ever.  Join us on ...