Splunk Search

Timechart woes

timrich66
Path Finder

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
Labels (1)
0 Karma
1 Solution

ITWhisperer
Legend

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.

View solution in original post

ITWhisperer
Legend

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.

0 Karma

timrich66
Path Finder

@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.

Tags (1)
0 Karma

ITWhisperer
Legend

JobName is not the field with the multi-values. Try

| foreach *
    [| mvexpand <<FIELD>>]
0 Karma

timrich66
Path Finder

This does not work.

I have tried with the field 'TimeStarted' before the eval statements and also with the field 'r' after the evals.

  • index=foo ...
    | foreach *
    [| mvexpand TimeStarted]
    | 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

AND

  • index=foo ...
    | 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)
    | foreach *
    [| mvexpand r]
    | timechart values(r) by JobName

 

0 Karma

ITWhisperer
Legend
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>>]
0 Karma

timrich66
Path Finder

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.

0 Karma

ITWhisperer
Legend

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
0 Karma

timrich66
Path Finder

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 🙂 

0 Karma

ITWhisperer
Legend

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.

View solution in original post

timrich66
Path Finder

Yes, you're right, thanks.  It was the 'chart' rather than 'timechart' that made the real difference.

All working as planned.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.