Splunk Search

Back-computation using table contents

vigneshtv
Explorer

I have categories.csv that contains list of sub-categories in each category

Category,Sub_category
Biology,Botany
Biology,Zoology
Physical_Science,Physics
Physical_Science,Chemistry

In another file I have the results for all sub-categories

Subject,Result
Botany,Pass
Zoology,Fail
Physics,Being_revaluted
Chemistry,Pass

I need to compute the overall result per category like

Biology,Physical_Science
Fail,Being_revaluted

Please help me achieve the above mentioned objective

I am able to extract the list of sub-categories in any particular category, say Biology, using the below code

source="/tmp/categories.csv" host="pc1" index="my_index" Category="Biology" | eval Sub_cat=Sub_category | search Category=Biology | table Category Sub_cat

The above code gives

Category,Sub_cat
Biology,Botony
Biology,Zoology

0 Karma

woodcock
Esteemed Legend

Something like this:

|inputlookup AnotherFileWithResults.csv
| lookup categories.csv Sub_category AS Subject
| chart count BY Category Result
0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Try this:

source="/tmp/categories.csv" host="pc1" index="my_index" Category="Biology" 
| fields Category Sub_category 
| append 
    [ search source="/tmp/sub-categories.csv" host="pc1" index="my_index" 
    | eval Sub_category= Subject 
    | fields Sub_category, Result] 
| stats values(*) as * by Sub_category
| xyseries Sub_category Category Result

vigneshtv
Explorer

Using your code, I am getting the results in terms of sub-categories like

Sub_category,Category,Result
Botany,Biology,Pass
Zoology,Biology,Fail ...

But I need them only in-terms of categories like

Biology,Physical_Science
Fail,Being_revaluted

Fail + Fail/Pass/Being_revaluted = Fail,
Pass + Pass = Pass,
Pass + Being_revaluted = Being_revaluted

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Try this:

source="/tmp/categories.csv" host="pc1" index="my_index" Category="Biology" 
| fields Category Sub_category 
| append 
    [ search source="/tmp/sub-categories.csv" host="pc1" index="my_index" 
    | eval Sub_category= Subject 
    | fields Sub_category, Result] 
| stats values(*) as * by Sub_category 
| stats values(Result) as Result by Category 
| eval Result=case(match(Result, "Fail"), "Fail", match(Result, "Being_revaluted"), "Being_revaluted", 1==1, "Pass")
0 Karma

vigneshtv
Explorer

*Minor changes to my code:

source="/tmp/categories.csv" host="pc1" index="my_index" Category=Biology | eval Subject=Sub_category | table Category Subject

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...