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?
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
… | 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.
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
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?
@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
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?
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
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
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
I think it doesnt work because time is in hh:mm:ss format...How do i convert it into seconds format?
Just remove
| eval TotalDuration=tostring(TotalDuration,"duration")
If this reply helps you, an upvote would be appreciated.
Thanks
Kamlesh Vaghela
Kamlesh,
It doesnt work ... graph seems to be empty. Can you test it at your end?
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
It doesnt work same thing as before
still doesnt work i tried without endwith ...It just displays status as RUNNING for all jobs and thats not correct status.
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
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
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
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?