Splunk Search

how to table job names at each time

pench2k19
Explorer

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

woodcock
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
Get Updates on the Splunk Community!

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...