Splunk Search
Highlighted

how to table job names at each time

Engager

Hi Guys,

I have the following query which i am showing line chart in a panel, how ever i want to show the jobnames at each _time.

Can you please help.

Query :

 index=infra_apps sourcetype=ca:atsys:edemon:txt  EventCode=40245 AND (Status=Failure OR Status=Terminated OR Status=Running OR Status=Success) AppID=CDH Machine=* Job=*
| dedup _raw 
| lookup datalakenodeslist.csv host as Machine OUTPUT cluster 
| search cluster=* 
| eval running=if(Status="RUNNING","1","0"),status=if(Status="RUNNING","start","stop"), time=_time+status 
| bin span=2m _time 
| stats max(running) as running, earliest(time) as first, latest(time) as last by Job,_time 
| xyseries _time Job running first last 
| makecontinuous span=2m _time 
| streamstats window=2 global=f earliest(last*) as last* 
| reverse 
| streamstats window=2 global=f earliest(first*) as first* 
| reverse 
| foreach running* 
    [ eval <<FIELD>>=if(isnull('<<FIELD>>') AND like('first<<MATCHSTR>>',"%start"),"0",if(isnull('<<FIELD>>') AND like('first<<MATCHSTR>>',"%stop"),"1",if(isnull('<<FIELD>>') AND like('last<<MATCHSTR>>',"%start"),"1",if(isnull('<<FIELD>>') AND like('last<<MATCHSTR>>',"%stop"),"0",'<<FIELD>>'))))] 
| fields - first*, last* 
| filldown * 
| reverse 
| filldown * 
| reverse 
| addtotals fieldname=RunningCount 
| fields _time,RunningCount

Query Statistical result:

_time                  RunningCount
3/19/2020 8:00     53
3/19/2020 8:02       44

Now i would like to see the names of jobs at each _time

Tags (2)
0 Karma
Highlighted

Re: how to table job names at each time

Esteemed Legend

Try this (I optimized some other stuff, too):

index="infra_apps" AND sourcetype="ca:atsys:edemon:txt" AND  EventCode="40245" AND (Status="Failure" OR Status="Terminated" OR Status="Running" OR Status="Success") AND AppID="CDH" AND Machine="*" AND Job="*"
| lookup datalakenodeslist.csv host AS Machine OUTPUT cluster 
| search cluster="*"
| eval running = if(Status="RUNNING","1","0"), status = if(Status="RUNNING", "start", "stop"), time = _time + status 
| timechart limit=0 useother=f usenull=f span=2m max(running) AS running, min(time) AS first, max(time) AS last by Job
| streamstats window=2 global=f earliest(last*) AS last* 
| reverse 
| streamstats window=2 global=f earliest(first*) AS first* 
| reverse 
| foreach running* 
    [ eval <<FIELD>>=if(isnull('<<FIELD>>') AND like('first<<MATCHSTR>>',"%start"),"0",if(isnull('<<FIELD>>') AND like('first<<MATCHSTR>>',"%stop"),"1",if(isnull('<<FIELD>>') AND like('last<<MATCHSTR>>',"%start"),"1",if(isnull('<<FIELD>>') AND like('last<<MATCHSTR>>',"%stop"),"0",'<<FIELD>>'))))] 
| fields - first*, last* 
| filldown * 
| reverse 
| filldown * 
| reverse 
| eval Jobs = ""
| foreach "running: *" [ eval Jobs = mvappend(Jobs, if('<<FIELD>>' > 0, "<<FIELD>>", null())) | fields - "<<FIELD>>" ]
| stats values(*) AS * BY _time
| replace "running: *" WITH * IN Jobs
| eval RunningCount = mvcount(Jobs)
0 Karma