perform math calculations on values with the same field name

[dang]

I have some data which shows the counts of items collected by category and subcategory. The data essentially looks like this:

Category     Subcategory     Results
---------    ------------    --------
Foo          Attempts        10
Foo          Failures        8
Foo          Successes       2

I can't seem to figure out how to work out the splunk query so I can find out the ratio of attempts to failures, when the data for all of these items has the same field name. Is this something can can easily done with "eval" or some other method?

[martin_mueller]

You can append this to your query that produced that table:

... | xyseries Category Subcategory Results | eval SuccessRatio = Successes / Attempts | eval FailureRatio = Failures / Attempts

Here's a full example along with simulation data:

| stats count | eval base="Foo-Attempts-10 Foo-Failures-8 Foo-Successes-2 Bar-Attempts-9 Bar-Failures-6 Bar-Successes-3" | makemv base | mvexpand base | rex field=base "(?<Category>\w+)-(?<Subcategory>\w+)-(?<Results>\w+)" | table Category Subcategory Results | xyseries Category Subcategory Results | eval SuccessRatio = Successes / Attempts

[martin_mueller]

I'm attempting to demonstrate that the xyseries command can turn this table

Category   Subcategory   Results
Foo        Attempts      10
Foo        Failures       8
Foo        Successes      2

into this table

Category   Attempts      Failures    Successes
Foo        10             8           2

Once you have the second table, you can do regular eval calculations based on the fields Attempts, Failures, and Successes.

[dang]

I'm not sure I fully understand what you're attempting to demonstrate here, but that's probably more an issue of my Splunk skills, rather than yours.

[linu1988 · Accepted solution]

Hello,
Try this

sourcetype=x SubCatagory="Attempts"|rename Results as AResults|join sourcetype[search sourcetype=x SubCatagory="Failures"|rename Results as FResults]|join sourcetype[search sourcetype=x SubCatagory="Failures"|rename Results as SResults]|eval SRatio=SResults/AResults|eval FRatio=FResults/AResults|table Catagory,AResults,FResults,SResults,SRatio,FRatio

Thanks

[dang]

This seems to get me headed in the direction I need to go, athough the better answer would likely be not having the data all named "Results"