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
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>>" ]
|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*