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
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)