topic Re: Basic search help in Splunk Search

Basic search help

splunk_zen — Mon, 25 Mar 2013 19:19:10 GMT

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)

| 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 ?

Re: Basic search help

gnovak — Mon, 28 Sep 2020 13:36:04 GMT

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

Re: Basic search help

splunk_zen — Mon, 28 Sep 2020 13:36:07 GMT

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 !

Re: Basic search help

splunk_zen — Mon, 25 Mar 2013 19:32:29 GMT

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)

Re: Basic search help

jonuwz — Tue, 26 Mar 2013 03:53:20 GMT

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)

Re: Basic search help

splunk_zen — Mon, 28 Sep 2020 13:36:22 GMT

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

Re: Basic search help

jonuwz — Mon, 28 Sep 2020 13:36:27 GMT

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

Re: Basic search help

splunk_zen — Tue, 26 Mar 2013 18:36:47 GMT

Thanks jonuwz.
I've you promote the comment to an answer I'll gladly accept it.

Re: Basic search help

jonuwz — Tue, 26 Mar 2013 19:02:15 GMT

incorporated into original answer.