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!

Splunk Observability Cloud | Customer Survey!

If you use Splunk Observability Cloud, we invite you to share your valuable insights with us through a brief ...

Happy CX Day, Splunk Community!

Happy CX Day, Splunk Community! CX stands for Customer Experience, and today, October 3rd, is CX Day — a ...

.conf23 | Get Your Cybersecurity Defense Analyst Certification in Vegas

We’re excited to announce a new Splunk certification exam being released at .conf23! If you’re going to Las ...