I've been searching and trying options for a couple of days now with this search and cannot find a solution.
I am using DB Connect to interrogate a database to get events that show me the start and end times for a suite of jobs. This works fine. Each day I have a single event detailing start and end time for each job. However, one of the jobs runs twice. I am trying to create a timechart showing the run time for each job. The job that runs twice gives two results for one day so when the timechart runs, the stats are there, but not the visualisation.
I'm sure there must be a simple solution to this, but I can't work it out. Is there a way to get the results to show in a timechart? Thanks in advance
My search -
index=foo sourcetype=bar JobName="BIDOFF" earliest=-7d@d latest=-0d@d+7h
| eval s=strptime(TimeStarted, "%Y-%m-%d %H:%M:%S.%Q")
| eval e=strptime(TimeCompleted, "%Y-%m-%d %H:%M:%S.%Q")
| eval r=(e - s)
| timechart values(r) by JobName
Results -
_time BIDOFF
2021-05-12 | 32.940000 33.000000 |
2021-05-13 | 33.013000 33.034000 |
2021-05-14 | 32.907000 33.110000 |
2021-05-15 | |
2021-05-16 | |
2021-05-17 | 32.936000 33.030000 |
2021-05-18 | 33.077000 34.547000 |
You don't need the streamstats or eventstats, they were there for a more generic solution rather than the hard-coded BIDOFF jobname. The essential element is to modify the job name for the different runs.
Using values() will give you a multi-value field - where there is more than one value for the same day for the same job, how do you expect to visualise this? The chart is expecting a single value per job per day. Rather than using values(), you could use sum() or max() or avg() depending on what it is you are trying to visualise.
@ITWhisperer - thanks - I should have made it clear that I wish to chart both values.
The options sum(), max() and avg() don't work for me. I've also tried "mvexpand JobName" but this doesn't help.
If I can only chart one, the first run at midday is the most important.
JobName is not the field with the multi-values. Try
| foreach *
[| mvexpand <<FIELD>>]
This does not work.
I have tried with the field 'TimeStarted' before the eval statements and also with the field 'r' after the evals.
AND
index=foo sourcetype=bar JobName="BIDOFF" earliest=-7d@d latest=-0d@d+7h
| eval s=strptime(TimeStarted, "%Y-%m-%d %H:%M:%S.%Q")
| eval e=strptime(TimeCompleted, "%Y-%m-%d %H:%M:%S.%Q")
| eval r=(e - s)
| timechart values(r) by JobName
| foreach *
[| mvexpand <<FIELD>>]
Thank you 🙂 I now have a line. What I need to do now is find out how to make it separate lines for each run.
To explain - each day the BIDOFF job runs at 12:00 and 13:00. Ideally, I would like a separate result for each run which then gives a separate line on the timechart.
Thanks for completing the picture
| streamstats count as index by _time job
| eventstats count as count by _time job
| eval job=if(count>1,job." ".index."/".count,job)
| chart values(value) as value by _time job
Your suggestion nearly worked, but not quite. I have taken your search and modified it as follows -
index=foo ...
| rex "Started=\"\d{4}-\d\d-\d\d (?<x>\d{2})"
| eval s=strptime(TimeStarted, "%Y-%m-%d %H:%M:%S.%Q")
| eval e=strptime(TimeCompleted, "%Y-%m-%d %H:%M:%S.%Q")
| eval r=(e - s)
| streamstats count as index by _time JobName
| eventstats count as count by _time JobName
| eval JobName=case(JobName!="BIDOFF",JobName,JobName="BIDOFF" AND x==12,JobName." - midday",JobName="BIDOFF" AND x==13,JobName." - 1pm")
| chart values(r) as "Run Time" by _time JobName
This works out 'r' (Run Time) and 'x' (the hour the job runs). Then, using a case statement, determines the JobName field based on the hour and the jobname. For job "BIDOFF' the supplemental time based comments are added. For all other jobnames, the name remains the same.
Thank you very much for this. Every day is a learning day 🙂
You don't need the streamstats or eventstats, they were there for a more generic solution rather than the hard-coded BIDOFF jobname. The essential element is to modify the job name for the different runs.
Yes, you're right, thanks. It was the 'chart' rather than 'timechart' that made the real difference.
All working as planned.