topic Overlay value in time chart in Splunk Search

Overlay value in time chart

tanasami — Wed, 18 Mar 2020 17:49:46 GMT

There are multiple programs running every day and I want to visualise the volume and duration of each program by day.

The data looks like this :

 _time, programtype, volume, daily
 20/01/2020,program1,8000,5444
 20/01/2020,program2,8000,1224
 21/01/2020,program1,1000,1123
 21/01/2020,program2,1000,1122

If i use a timechart, (e.g. | timechart span=1d values(volume) sum(duration) by programtype to display the data, I would get 4 graphs being shown.

VolumeProgram1
VolumeProgram2
DurationProgram1
DurationProgram2

What i want to show is, 3 graphs with the volume being generic.
*All programs will always have the same volume on the day.
How would the query look like if i want to show :

Volume
DurationProgram1
DurationProgram2

Is there a way to merge the data ?

Thanks

Re: Overlay value in time chart

to4kawa — Wed, 18 Mar 2020 18:51:27 GMT

|makeresults
| eval _raw="_time, programtype, volume, daily
20/01/2020,program1,8000,5444
20/01/2020,program2,8000,1224
21/01/2020,program1,1000,1123
21/01/2020,program2,1000,1122"
| rex mode=sed "s/(?m)^\s+//g"
| multikv forceheader=1
| eval _time=strptime(time,"%d/%m/%Y")
| table _time, programtype, volume, daily
| rename COMMENT as "this is sample you provide. From here, the logic"
| timechart span=1d values(volume) as volume sum(daily) as duration by programtype sep=""
| foreach volume* [ eval Volume = max('<<FIELD>>') ]
| rename duration* as *
| fields - volume*

Re: Overlay value in time chart

woodcock — Sun, 22 Mar 2020 19:26:40 GMT

First of all, values() is not a proper aggregation function for timechart and it really should give you an error (if it does not, that is a bug on Splunk's part), so let's start by switching to avg() (you might prefer max() or something else). So try this:

| timechart span=1d avg(volume) AS volume sum(duration) AS duration BY programtype
| eval volume = 0
| foreach "volume:*" [ eval volume = volume + '<<FIELD>>' | fields - "<<FIELD>>" ]

Here is a run-anywhere example:

|makeresults | eval _raw="_time          programtype  volume    daily
20/01/2020     program1     8000      5444
20/01/2020     program2     8000      1224
21/01/2020     program1     1000      1123
21/01/2020     program2     1000      1122"
| multikv forceheader=1
| eval _time = strptime(time, "%d/%m/%Y")
| rename daily AS duration
| timechart fixedrange=f span=1d avg(volume) AS volume sum(duration) AS duration BY programtype
| eval volume = 0
| foreach "volume:*" [ eval volume = volume + '<<FIELD>>' | fields - "<<FIELD>>" ]