I've got a custom source A and B, which I need to compute a weighted average over,
each source has only 2 collums: datetime and its own score (one per day, both sources times coincide).
EDIT:
EtuServiceApdex.csv headers:
_time,EtuServiceApdex
EtuOpApdex.csv headers:
_time,EtuOpApdex
index=resources_reporting (source="*/output/ora_queries/client07/EtuOpApdex.csv" OR source="*/output/ora_queries/client07/EtuServiceApdex.csv")
returns something like,
28/02/2013 00:00:00.000 2013-02-28T00:00:00.000+00:00,64.583333
host=vsplunk sourcetype=csv-47 source=/opt/splunk/etc/apps/remote_scripts/output/ora_queries/client07/EtuServiceApdex.csv
28/02/2013 00:00:00.000 2013-02-28T00:00:00.000+00:00,50
host=vsplunk sourcetype=csv-49 source=/opt/splunk/etc/apps/remote_scripts/output/ora_queries/client07/EtuOpApdex.csv
27/02/2013 00:00:00.000 2013-02-27T00:00:00.000+00:00,77.083333
host=vsplunk sourcetype=csv-47 source=/opt/splunk/etc/apps/remote_scripts/output/ora_queries/client07/EtuServiceApdex.csv
27/02/2013 00:00:00.000 2013-02-27T00:00:00.000+00:00,54.166667
host=vsplunk sourcetype=csv-49 source=/opt/splunk/etc/apps/remote_scripts/output/ora_queries/client07/EtuOpApdex.csv
and both
index=resources_reporting (source="*/output/ora_queries/mt/MT_EtuOpApdex.csv" OR source="*/output/ora_queries/client07/EtuServiceApdex.csv")
| timechart avg(EtuOpApdex)
or
| timechart avg(EtuServiceApdex)
return the expected graph over time,
however, If I add the following I lose the _time properties
| eval TotalApdexScore=(0.66*EtuServiceApdex + 0.33*EtuOpApdex)
and
| timechart avg(TotalApdexScore)
returns a _time series with null values over TotalApdexScore.
Eg:
_time avg(TotalApdexScore)
1 01/01/2013 00:00:00.000
2 02/01/2013 00:00:00.000
3 03/01/2013 00:00:00.000
How should I change the expression to get the intended weighted average over time ?
There are no fields in your raw data called EtuOpApdex or EtuServiceApdex, so
| timechart avg(EtuOpApdex)
or
| timechart avg(EtuServiceApdex)
is never going to work.
As a human I had to look at your raw data pretty closely to figure out where the value is ( the number after the "," after the XML-stantard datetime, and what the key was. The alphanumeric characters preceeding the ".csv" in the source.
Splunk isnt going to magically know this. You have to extract these fields up front.
index=resources_reporting (source="*/output/ora_queries/client07/MT_EtuOpApdex.csv" OR source="*/output/ora_queries/client07/EtuServiceApdex.csv")
| rex ".*\d{4}-\d{2}-\d{2}T[^,]+,(?<value>[\d.]+)"
| rex field=source ".*?(?<field>[A-Za-z]+)\.csv"
| table _time field value
make sure the fields are extracted properly
now you can do
| timechart span=1d avg(value) by field
and now the eval to get the weighted average should work
| eval TotalApdexScore=(0.66*EtuServiceApdex + 0.33*EtuOpApdex)
Update
The reason using stats 1st works is that it puts EtuServiceApdex and EtuOpApdex in the same event.
Until you do that, only 1 of EtuServiceApdex or EtuOpApdex will exist and the other will be NULL
Do this to prove it : ... | table _time EtuOpApdex EtuServiceApdex
If either is NULL, (0.66EtuServiceApdex) + (0.33EtuOpApdex) will evaluate to NULL
An alternative is to this is to do the eval after the timechart.
| timechart avg(EtuOpApdex) as EtuOpApdex avg(EtuServiceApdex) as EtuServiceApdex
| eval TotalApdexScore=(0.66*EtuServiceApdex + 0.33*EtuOpApdex)
There are no fields in your raw data called EtuOpApdex or EtuServiceApdex, so
| timechart avg(EtuOpApdex)
or
| timechart avg(EtuServiceApdex)
is never going to work.
As a human I had to look at your raw data pretty closely to figure out where the value is ( the number after the "," after the XML-stantard datetime, and what the key was. The alphanumeric characters preceeding the ".csv" in the source.
Splunk isnt going to magically know this. You have to extract these fields up front.
index=resources_reporting (source="*/output/ora_queries/client07/MT_EtuOpApdex.csv" OR source="*/output/ora_queries/client07/EtuServiceApdex.csv")
| rex ".*\d{4}-\d{2}-\d{2}T[^,]+,(?<value>[\d.]+)"
| rex field=source ".*?(?<field>[A-Za-z]+)\.csv"
| table _time field value
make sure the fields are extracted properly
now you can do
| timechart span=1d avg(value) by field
and now the eval to get the weighted average should work
| eval TotalApdexScore=(0.66*EtuServiceApdex + 0.33*EtuOpApdex)
Update
The reason using stats 1st works is that it puts EtuServiceApdex and EtuOpApdex in the same event.
Until you do that, only 1 of EtuServiceApdex or EtuOpApdex will exist and the other will be NULL
Do this to prove it : ... | table _time EtuOpApdex EtuServiceApdex
If either is NULL, (0.66EtuServiceApdex) + (0.33EtuOpApdex) will evaluate to NULL
An alternative is to this is to do the eval after the timechart.
| timechart avg(EtuOpApdex) as EtuOpApdex avg(EtuServiceApdex) as EtuServiceApdex
| eval TotalApdexScore=(0.66*EtuServiceApdex + 0.33*EtuOpApdex)
incorporated into original answer.
Thanks jonuwz.
I've you promote the comment to an answer I'll gladly accept it.
Because in any given line, only 1 of EtuServiceApdex and EtuOpApdex exists.
Do ... | table _time EtuOpApdex EtuServiceApdex
If either is NULL, (0.66*EtuServiceApdex) + (0.33*EtuOpApdex) will evaluate to NULL
I'm sorry jonuwz.
My original question had a missing information bit which ended up forcing you to generate that REGEX.
The .csv sources headers are respectively:
_time,EtuServiceApdex
_time,EtuOpApdex
which is why splunk seamlessly generates the following charts,
| timechart avg(EtuServiceApdex)
| timechart avg(EtuOpApdex)
but I can't understand why it is failing with
| eval TotalApdexScore=(0.66*EtuServiceApdex + 0.33*EtuOpApdex)
until I precede it with
| stats avg(EtuServiceApdex) as EtuServiceApdex avg(EtuOpApdex) as EtuOpApdex by _time
gnovak, that line is a simplified expresion of my use case, I need a weighted score, in the mentioned expression EtuServiceApdex has a weight of 66% and EtuOpApdex a weight of 33% (both range from 0 to 100, yeah I know 33 + 66 is not 100)
Update:
I've added
| stats avg(EtuServiceApdex) as EtuServiceApdex avg(EtuOpApdex) as EtuOpApdex by _time
getting this total expression
index=resources_reporting (source="/output/ora_queries/client07/MT_EtuOpApdex.csv" OR source="/output
| stats avg(EtuServiceApdex) as EtuServiceApdex avg(EtuOpApdex) as EtuOpApdex by _time
| eval TotalApdexScore=(0.66*EtuServiceApdex + 0.33*EtuOpApdex)
| timechart avg(TotalApdexScore)
And I got the _time properties on both components,
but I do not understand why I needed to add this | stats !
What exactly are you trying to do with this line? | eval TotalApdexScore=(0.66*EtuServiceApdex + 0.33*EtuOpApdex). I think I get it...maybe try changing the syntax to eval NewValue=(FirstValue*.60)+(SecondValue*.40) or something like that