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
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)
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)
Many thanks!!
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)..
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.