Hello,
In my dashboard I have defined a multiselect field with the following possible values:
dt1, dt2, dt3 and total
Now, I would like to use them in my search in the aggregation functions (avg) passing them with the kpi token. However I have an issue with the aggregation function themself as they are not able to pick up the VALUES of the newly created fields f1, .., fn.
I was thinking of sth like below in my search:
index=mlbso sourcetype=webdispatcher
| eval kpi = "dt3 total dt1 dt2 dt4"
| rex field=kpi "(?P<f1>dt1|dt2|dt3|dt4|total) (?P<f2>dt1|dt2|dt3|dt4|total) (?P<f3>dt1|dt2|dt3|dt4|total) (?P<f4>dt1|dt2|dt3|dt4|total) (?P<f5>dt1|dt2|dt3|dt4|total)"
| timechart span=15m
avg(f1) as avg_server,
avg(f2) as avg_total
by "DBSID"
but the avg does not recognize the value of f1 and f2 as an argument.
How would I do this in the best way?
Kind Regards,
Kamil
If I understand your requirement correctly, you are looking to control the timechart series that are displayed, based on the columns you specify in your multiselect.
In that case, you can used the fields statement to only include the _time and DBSID field AND also the token value fields before your timechart command. Then in the timechart, you still do all the aggregations you want in, the missing fields will not appear in the output. Note that the token values in this example have the * character.
<form>
<label>conditional column</label>
<fieldset submitButton="false">
<input type="multiselect" token="columns" searchWhenChanged="true">
<label>Columns</label>
<choice value="timeendpos*">timeendpos</choice>
<choice value="timestartpos*">timestartpos</choice>
<delimiter> </delimiter>
</input>
</fieldset>
<row>
<panel>
<chart>
<search>
<query>index=_internal
| fields _time sourcetype $columns$
| timechart span=1m avg(timeendpos) as timeendpos avg(timestartpos) as timestartpos by sourcetype
</query>
<earliest>-15m</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="charting.chart">line</option>
<option name="charting.drilldown">none</option>
<option name="refresh.display">progressbar</option>
</chart>
</panel>
</row>
</form>
This example just looks at two field names timeendpos and timestart pos in the _internal index data. The multiselect will set the token to be the field names and then this token value is used in the fields statement.
Hope this helps.
If I understand your requirement correctly, you are looking to control the timechart series that are displayed, based on the columns you specify in your multiselect.
In that case, you can used the fields statement to only include the _time and DBSID field AND also the token value fields before your timechart command. Then in the timechart, you still do all the aggregations you want in, the missing fields will not appear in the output. Note that the token values in this example have the * character.
<form>
<label>conditional column</label>
<fieldset submitButton="false">
<input type="multiselect" token="columns" searchWhenChanged="true">
<label>Columns</label>
<choice value="timeendpos*">timeendpos</choice>
<choice value="timestartpos*">timestartpos</choice>
<delimiter> </delimiter>
</input>
</fieldset>
<row>
<panel>
<chart>
<search>
<query>index=_internal
| fields _time sourcetype $columns$
| timechart span=1m avg(timeendpos) as timeendpos avg(timestartpos) as timestartpos by sourcetype
</query>
<earliest>-15m</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="charting.chart">line</option>
<option name="charting.drilldown">none</option>
<option name="refresh.display">progressbar</option>
</chart>
</panel>
</row>
</form>
This example just looks at two field names timeendpos and timestart pos in the _internal index data. The multiselect will set the token to be the field names and then this token value is used in the fields statement.
Hope this helps.
Perfect, this is precisely what I wanted to achieve. Thank you.
I would not think it goes so straightforward, I wanted to overcomplicate it a bit ...
Note that the solution given by @ITWhisperer is also a good one and shows you how you can use the value part of an input to generate aggregations used by any subsequent stats/timechart command
why are you creating new fields(f1,f2,f3...) for same field(kpi) and values(dt1|dt2|dt3|dt4|total)?
confirm if you are trying to find out average of dt1 or dt2 (what ever you chose from multiselect) and that's available in your index data?
I confirm.
I want to make a chart with averages over the fields dt1, dt2, dt3, dt4, total. These fields are available in my index. I am creating the f1, f2 ... from the multiselect field token kpi as this is the best that came up to my mind. The kpi token looks like e.g. "dt3 dt1 dt2", so how would I put it into the search in the form I want otherwise?
<input type="multiselect" token="kpi" searchWhenChanged="true">
<label>KPI</label>
<choice value="avg(dt1) as avg_dt1">dt1</choice>
<choice value="avg(dt2) as avg_dt2">dt2</choice>
<choice value="avg(dt3) as avg_dt3">dt3</choice>
<choice value="avg(total) as avg_total">total</choice>
<delimiter> </delimiter>
</input>
Then
<panel>
<chart>
<search>
<query>index=mlbso sourcetype=webdispatcher
| timechart span=15m
$kpi$
by DBSID
</query>