topic Re: Calculated the percent difference between two values in Reporting

Calculated the percent difference between two values

Branden — Fri, 01 Oct 2010 20:51:26 GMT

This is a tricky one (or is it?)...

I have indexed Splunk data that looks like this (using multikv):

device_name     host    list(Select)    list(Disk)      difference
vpath0        xyz     19072176        fscsi2/hdisk28  13409 
                        19058767        fscsi3/hdisk56

I'd like to be alerted if the difference between the two "list(Select)" fields is greater than 5%. I know how to do the math on paper, but I can't figure out how to apply it to Splunk. Essentially, I need to divide the "difference" by value of the higher "list(Select)", then multiply by 100.

I've been dabbling with this, but now I'm wondering if it's even possible.

Thanks!

Re: Calculated the percent difference between two values

ftk — Fri, 01 Oct 2010 21:59:02 GMT

You can use the eval command:

your search |  eval percent_difference=(difference/max(list(Select))*100)

Then set up a custom alert condition that hits when percent_difference > 5.

If this doesn't work try renaming your list(Select) to a more friendly name (without parenthesis).

[Edit:] Ok, playing around with your query and my data I noticed that the list(Select) didn't return a numerical value list for me, which caused the calculation of percent_difference to fail. Try adding a tonumber() (added in 4.1.4 or 4.1.5, so you need a recent version for this to work) into your query as such:

sourcetype="datapath-device" earliest=-2h| rex "^DEV#:\s+(?<dev_no>\d+)\s+DEVICE NAME:\s+(?<device_name>\S+)\s+TYPE:\s+(?<type>\d+)\s+POLICY:\s+(?<policy>\S+)" | rex "SERIAL:\s+(?<serial>\S+)" | multikv | stats list(Select) as listSelect, list(Disk), range(Select) as difference by device_name, host | eval percent_difference=((difference/max(tonumber(listSelect)))*100)

If that doesn't work, use eventstats instead of regular stats as such:

sourcetype="datapath-device" earliest=-2h| rex "^DEV#:\s+(?<dev_no>\d+)\s+DEVICE NAME:\s+(?<device_name>\S+)\s+TYPE:\s+(?<type>\d+)\s+POLICY:\s+(?<policy>\S+)" | rex "SERIAL:\s+(?<serial>\S+)" | multikv | eventstats list(Select) as listSelect, list(Disk), range(Select) as difference by device_name, host | eval percent_difference=((difference/max(tonumber(listSelect)))*100)

[Edit2] What about this:

sourcetype="datapath-device" earliest=-2h| rex "^DEV#:\s+(?<dev_no>\d+)\s+DEVICE NAME:\s+(?<device_name>\S+)\s+TYPE:\s+(?<type>\d+)\s+POLICY:\s+(?<policy>\S+)" | rex "SERIAL:\s+(?<serial>\S+)" | multikv | stats list(Select) as listSelect, list(Disk), range(Select) as difference by device_name, host | eventstats max(listSelect) as maxSelect | eval percent_difference=((difference/maxSelect)*100)

[EDIT 3:] how about streamstats?

sourcetype="datapath-device" earliest=-2h| rex "^DEV#:\s+(?<dev_no>\d+)\s+DEVICE NAME:\s+(?<device_name>\S+)\s+TYPE:\s+(?<type>\d+)\s+POLICY:\s+(?<policy>\S+)" | rex "SERIAL:\s+(?<serial>\S+)" | multikv | stats list(Select) as listSelect, list(Disk), range(Select) as difference by device_name, host | streamstats max(listSelect) as maxSelect window=1 | eval percent_difference=((difference/maxSelect)*100)

Re: Calculated the percent difference between two values

Branden — Fri, 01 Oct 2010 22:39:54 GMT

Thank you very much for your reply.
I had to change the list(Select) field name like you said, no big deal.
I ran the query with the eval command, but I don't see the result anywhere. Shouldn't percent_difference show up as a field?

Re: Calculated the percent difference between two values

Branden — Fri, 01 Oct 2010 22:42:36 GMT

I also modified the eval command a bit to look like this:

eval percent_difference=((difference/max(listSelect))*100)

Still not sure why it doesn't appear as a field "percent_difference" though...

Re: Calculated the percent difference between two values

ftk — Fri, 01 Oct 2010 22:50:09 GMT

@Branden, try adding a | table percent_difference at the end of your search. If it calculates it it should show.

Re: Calculated the percent difference between two values

ftk — Fri, 01 Oct 2010 22:51:15 GMT

Oh yeah and oops, not sure why i put a - in there instead of a /. Brain fart I guess 🙂

Re: Calculated the percent difference between two values

Branden — Fri, 01 Oct 2010 23:14:37 GMT

Hmmm.... not showing any results when I do that. I wonder if it's not calculating right...

Re: Calculated the percent difference between two values

Branden — Mon, 28 Sep 2020 09:18:29 GMT

If it helps, here's my exact query:

sourcetype="datapath-device" earliest=-2h| rex "^DEV#:\s+(?\d+)\s+DEVICE NAME:\s+(?\S+)\s+TYPE:\s+(?\d+)\s+POLICY:\s+(?\S+)" | rex "SERIAL:\s+(?\S+)" | multikv | stats list(Select) as listSelect, list(Disk), range(Select) as difference by device_name, host | eval percent_difference=((difference/max(listSelect))*100)

Re: Calculated the percent difference between two values

ftk — Sat, 02 Oct 2010 00:26:42 GMT

@ Branden I edited the answer a bit based on your query since this is rather long...

Re: Calculated the percent difference between two values

Branden — Sat, 02 Oct 2010 00:35:03 GMT

Thank you again for the help.
Unfortunately, neither solution works. "tonumber()" didn't make a difference (I'm running 4.1.5).. and eventstats destroys the formatting of the output, and it still doesn't show percent_difference.
Is it safe to say that the reason it's not working is because it can't do the calculation?

Re: Calculated the percent difference between two values

Branden — Sat, 02 Oct 2010 00:43:06 GMT

Okay I think I know what the problem is. I changed the calculation to this for testing purposes:
"| eval percent_difference=(max(listSelect))"
I wanted to see if it could at least pick out the higher of the two listSelects. It can't. Instead, it just lists both listSelects values. It's as if the two listSelects are from two separate searches or something...

Re: Calculated the percent difference between two values

ftk — Sat, 02 Oct 2010 00:43:13 GMT

@Branden yes I believe that is pretty safe to say. What about switching max and tonumber? as in eval percent_difference=((difference/tonumber(max(listSelect)))*100

Re: Calculated the percent difference between two values

Branden — Sat, 02 Oct 2010 00:49:33 GMT

And I think part of the problem too is that listSelect isn't a number; it doesn't have that 'n' next to the field. I know we tried wrapping it around that tonumber() function, but for some reason it's not taking.
(My indexer is running 4.1.5, but the forwarder that is capturing this data is running 4.1.4. The forwarder version shouldn't matter, right?)

Re: Calculated the percent difference between two values

ftk — Sat, 02 Oct 2010 00:51:00 GMT

Forwarder version should not matter.

Re: Calculated the percent difference between two values

ftk — Sat, 02 Oct 2010 00:52:37 GMT

I think part of the problem is the list() command. It looks like max just doesnt work on its output...

Re: Calculated the percent difference between two values

ftk — Sat, 02 Oct 2010 00:59:18 GMT

Did another answer edit. What about calculating the max(listSelect) via eventstats before the eval? works in my test..

Re: Calculated the percent difference between two values

Branden — Sat, 02 Oct 2010 00:59:37 GMT

GOT IT!
I looked closer at the list of fields... and there was a "Select(n)" field there which corresponded to the values in listSelect. I don't know why both were listed, but I don't particularly care at this point. 🙂
Anyways, I plugged in "Select" instead of "listSelect" and it worked beautifully!
| eval percent_difference=((difference/(max(Select)))*100)

Thank you SO much for your help! 🙂

Re: Calculated the percent difference between two values

Branden — Sat, 02 Oct 2010 01:03:13 GMT

Your third edit above worked sorta... the only problem is that it took the max(listSelect) of all the indexed results, not just the listSelect for that one entry. (I hope that made sense).
Regardless, I think I have the problem fixed now (see comment above). Thanks again, I couldn't have done this with your help and patience.

Re: Calculated the percent difference between two values

Branden — Sat, 02 Oct 2010 01:10:33 GMT

I love how your Edit2 formats it though... If I can get it to grab the correct max(listSelect) setting, that would be golden. I'll post my results here when I figure it out. Thanks again!

Re: Calculated the percent difference between two values

ftk — Sat, 02 Oct 2010 01:12:13 GMT

Glad to help 🙂