Getting Data In

How to get current status on the following Splunk query transaction command?

Csingh
Engager

Hi everyone,

I have a  question for following sample events. I am trying to group by job and provide two things current status of job (i.e starting/running/success/failure) and total duration time for job i.e duration between starting, running and success phases of job that might be run multiple times in a day.

 JOBNAME           STATUS      TOTALDURATION

JOB1                RUNNING     00:30:10(ran for the day)

JOB2                SUCCESS     01:20:10(ran for the day)

SAMPLE EVENTS:

[04/07/2021 22:16:01].  EVENT: CHANGESTATUS  STATUS: SUCCESS   JOB: JOB1

[04/07/2021 22:15:01].  EVENT: CHANGESTATUS  STATUS: RUNNING   JOB: JOB1

[04/07/2021 22:15:00].  EVENT: CHANGESTATUS  STATUS: STARTING   JOB: JOB1

[04/07/2021 22:11:01].  EVENT: CHANGESTATUS  STATUS: SUCCESS   JOB: JOB1

[04/07/2021 22:10:08].  EVENT: CHANGESTATUS  STATUS: SUCCESS   JOB: JOB2

[04/07/2021 22:10:01].  EVENT: CHANGESTATUS  STATUS: RUNNING   JOB: JOB2

[04/07/2021 22:10:01].  EVENT: CHANGESTATUS  STATUS: RUNNING   JOB: JOB1

[04/07/2021 22:10:00].  EVENT: CHANGESTATUS  STATUS: STARTING   JOB: JOB1

[04/07/2021 22:10:00].  EVENT: CHANGESTATUS  STATUS: STARTING   JOB: JOB2

Query:

index=.........|rex " query captures status , jobname and timestamp in format HH:MM:SS"|transaction jobname startswith=(status="STARTING") endswith=(status="SUCCESS")|stats first(status) as jobcurrentstatus , sum(duration) as totaldur by jobname| eval totalduration(HH:MM:SS)=tostring(totaldur,"duration")

My current query works for TOTALDURATION but doesn't give accurate result for my current job status.Is there a way i can get correct current status?

Labels (1)
Tags (2)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Csingh 

 

Can you please share sample OP from this search?

YOUR_SEARCH
| transaction JOB startswith="STARTING" 
| eventstats sum(duration) as TotalDuration by JOB 
| fields TotalDuration JOB STATUS 
| dedup JOB 
| eval TotalDuration=tostring(TotalDuration,"duration") 
| table JOB STATUS TotalDuration

View solution in original post

0 Karma

htmlkick
Observer
… | transaction trade_id
| chart count by duration
… | stats range(_time) as duration by trade_id
| chart count by duration
The second search is more efficient.

Read More 

Tags (1)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Csingh 

 

After trying many combinations with transaction command I've design below search with one assumption. 

 

The assumption is : The status in the log will be STARTING then RUNNING and finally SUCCESS. With this assumption I have added 

| eval STATUS = case(mvcount(STATUS)==1,"STARTING ",mvcount(STATUS)==2,"RUNNING",1=1,"SUCCESS") 

 

So please try this. 

YOUR_SEARCH
| transaction JOB startswith="STARTING" 
| eventstats sum(duration) as TotalDuration by JOB 
| fields TotalDuration JOB STATUS 
| dedup JOB 
| eval TotalDuration=tostring(TotalDuration,"duration") 
| eval STATUS = case(mvcount(STATUS)==1,"STARTING ",mvcount(STATUS)==2,"RUNNING",1=1,"SUCCESS") 
| table JOB STATUS TotalDuration

 

0 Karma

Csingh
Engager

Thx for reply. I just ran and it shows only two jobs with success status...what are we doing in the eval command you just added?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Csingh 

This is for managing sequence of STARTING, RUNNING and SUCCESS.
When you do transaction, STATUS field will be multivalued and the value will be in alphabetical order. mens it will show you like RUNNING, STARTING, SUCCESS. So to have original value I have added this eval.  if you observe eval, with my assumption, I have focus on the count of multi value. if the count is 1 then Job is just started, if 2 then it's running else it is completed.  It is working with whatever sample data you provided on question.

 

For more assistance , can you please provided the search you modified and sample OP?

 

If this reply helps you, an upvote would be appreciated.

 

Thanks
Kamlesh Vaghela

0 Karma

Csingh
Engager

Hi kamlesh,

 

Its showing me SUCCESS for all the jobs even if job is currently in RUNNING state. Do you know why that would be?

 

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Csingh 

 

Can you please share sample OP from this search?

YOUR_SEARCH
| transaction JOB startswith="STARTING" 
| eventstats sum(duration) as TotalDuration by JOB 
| fields TotalDuration JOB STATUS 
| dedup JOB 
| eval TotalDuration=tostring(TotalDuration,"duration") 
| table JOB STATUS TotalDuration
0 Karma

Csingh
Engager

Thanks kamlesh it works now ...i forgot to delete endswith part.  I will mark your solution as approved.One more think i would like to add timechart graph to below query how do i do that? My timechart command doesnt work.

 

 

index=.........|rex " query captures status , jobname and timestamp in format HH:MM:SS"|transaction jobname startswith=(status="STARTING")|eventstats sum(duration) as totalduration by jobname| fields totoalduration jobName status |dedup jobName|eval totalduration=tostring(totalduration,"duration")|eval status = case(mvcount(status)==1,"STARTING",mvcount(status)==2,"RUNNING",1=1,"SUCCESS") |table jobName status totalduration|timechart list(totalduration) by jobName

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Csingh 

 

Just remove table and use fields command like..

 

index=......... 
| rex " query captures status , jobname and timestamp in format HH:MM:SS" 
| transaction jobname startswith=(status="STARTING") 
| eventstats sum(duration) as totalduration by jobname 
| fields totoalduration jobName status 
| dedup jobName 
| eval totalduration=tostring(totalduration,"duration") 
| eval status = case(mvcount(status)==1,"STARTING",mvcount(status)==2,"RUNNING",1=1,"SUCCESS") 
| fields jobName status totalduration 
| timechart list(totalduration) by jobName

 

If this reply helps you, an upvote would be appreciated.

Thanks
Kamlesh Vaghela

 

0 Karma

Csingh
Engager

I think it doesnt work because time is in hh:mm:ss format...How do i convert it into seconds format?

Tags (1)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Csingh 

Just remove 

| eval TotalDuration=tostring(TotalDuration,"duration") 

 

If this reply helps you, an upvote would be appreciated.

Thanks
Kamlesh Vaghela 

0 Karma

Csingh
Engager

Kamlesh,

It doesnt work ... graph seems to be empty. Can you test it at your end?

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Csingh,

please, try adding to the transaction command the following option:

keeporphans=true

you can find more infos at https://docs.splunk.com/Documentation/Splunk/8.1.3/SearchReference/Transaction

Ciao.

Giuseppe

 

Csingh
Engager

It doesnt work same thing as before

0 Karma

gcusello
SplunkTrust
SplunkTrust

HI @Csingh,

please try 

| transaction jobname startswith="STARTING"

 withou endwith.

Ciao.

Giuseppe

0 Karma

Csingh
Engager

still doesnt work i tried without endwith ...It just displays status as RUNNING for all jobs and thats not correct status.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Csingh,

let me understand: you want the last status of each job, is it correct?

if you want the last status use "last" instead "first" in your stats command.

Another question: job_value is the same from start to success, but coult it be the same in more jobs?

Ciao.

Giuseppe

0 Karma

Csingh
Engager

if you look at look at  sample events each job could be run multiple times. a day. I just gave a sample.Actually there are about 20 jobs running multiple times a day.  What i want is Totaldurationtime  for a particular job (i.e if job 1 ran 10 times today for 2 seconds each.).. and its current run status

                TOTALDURATION                          CURRENTRUNSTATUS

JOB1        20seconds(00:00:20)                (STARTING/RUNNING/SUCCESS)

 

runtime for each time could be different everytime could be 2 seconds for one job but next time could be 3seconds or even one hour

I NEED THIS FOR ALL JOBS 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Csingh,

duration isn't a problem, also because when you use the transaction command you automatically have the duration field.

about last status, please try something like this:

index=.........
| rex " query captures status , jobname and timestamp in format HH:MM:SS"
| transaction jobname startswith=(status="STARTING")
| stats last(status) as jobcurrentstatus sum(duration) as totaldur by jobname
| eval totalduration(HH:MM:SS)=tostring(totaldur,"duration")

 Ciao.

Giuseppe

0 Karma

Csingh
Engager

when i use last status i get all jobs status as SUCCESS. I have also noticed when i remove transaction command it seems to work but i need transaction command to get duration...Is there any alternative?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...