topic Re: Timechart woes in Splunk Search

Timechart woes

timrich66 — Wed, 19 May 2021 09:36:40 GMT

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

Re: Timechart woes

ITWhisperer — Wed, 19 May 2021 09:56:30 GMT

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.

Re: Timechart woes

timrich66 — Wed, 19 May 2021 10:21:45 GMT

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

Re: Timechart woes

ITWhisperer — Wed, 19 May 2021 10:38:24 GMT

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

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

Re: Timechart woes

timrich66 — Wed, 19 May 2021 11:10:49 GMT

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

Re: Timechart woes

ITWhisperer — Wed, 19 May 2021 11:16:41 GMT

Re: Timechart woes

timrich66 — Wed, 19 May 2021 11:43:22 GMT

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.

Re: Timechart woes

ITWhisperer — Wed, 19 May 2021 12:37:58 GMT

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

Re: Timechart woes

timrich66 — Wed, 19 May 2021 15:00:40 GMT

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 🙂

Re: Timechart woes

ITWhisperer — Wed, 19 May 2021 15:06:43 GMT

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.

Re: Timechart woes

timrich66 — Thu, 20 May 2021 08:37:21 GMT

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

All working as planned.