topic Re: Get the difference of 2 columns after Grouping in Splunk Search

Get the difference of 2 columns after Grouping

GadgetGeek — Thu, 06 Aug 2015 10:42:40 GMT

After grouping to display a chart, where there are only 2 values produced, how do I calculate the difference between the 2 columns?

e.g. Sample search :

<search terms> | rex "Sent\s+(?<ProductType>[a-zA-Z]*)" | rex "Product:\s+(?<ProductId>[a-zA-Z0-9]{5}-[a-zA-Z0-9]{8})" | eval ProductType=if(ProductType="Apple", "Fruit", "Veg")  | chart count(ProductType) as Total by ProductId, ProductType

Giving:

ProductId                Fruit      Veg
12345-12345678            3          2

Where I want:

ProductId                Fruit      Veg  Diff
12345-12345678            3          2    1

Re: Get the difference of 2 columns after Grouping

somesoni2 — Thu, 06 Aug 2015 14:58:09 GMT

If the ProductType is static, then the field name after chart will also be fixed, you can just use an eval to get the difference.

<search terms> | rex "Sent\s+(?<ProductType>[a-zA-Z]*)" | rex "Product:\s+(?<ProductId>[a-zA-Z0-9]{5}-[a-zA-Z0-9]{8})" | eval ProductType=if(ProductType="Apple", "Fruit", "Veg")  | chart count(ProductType) as Total by ProductId, ProductType | eval Diff=abs(Fruit-Veg)

Re: Get the difference of 2 columns after Grouping

GadgetGeek — Thu, 06 Aug 2015 15:21:53 GMT

It works - how does it work though, as "Fruit" and "Veg" are string values for 'ProductType'? It's kind of like:

Diff=abs(ProductType="Fruit", ProductType="Veg)..

Re: Get the difference of 2 columns after Grouping

somesoni2 — Thu, 06 Aug 2015 15:57:31 GMT

After your chart command, the values of Product Type becomes field names. So you'll have actual field names like 'Fruit' and 'Veg', that what's been used for calculation.

Re: Get the difference of 2 columns after Grouping

GadgetGeek — Fri, 07 Aug 2015 05:00:16 GMT

Many thanks!!