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
Champion

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
Champion

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
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...