Splunk Search

Splunk case statement to count values based on field name

anantdeshpande
Path Finder

Hi team,
there are three fields in source "app1.csv" (CUST_ID,ACCT_ID,SUBSCRIP_ID). There is no other field in this table to differentiate these fields.
I want to showcase count of each ID field separately.

index="test" | eval SCHEDULED_DELETIONS=case(
source="app1.csv" AND ACTION="SCHEDULED" AND isnotnull(CUST_ID),"CUSTOMER_ID",
source="app1.csv" AND ACTION="SCHEDULED" AND isnotnull(ACCT_ID),"ACCOUNT_ID",
source="app1.csv" AND ACTION="SCHEDULED" AND isnotnull(SUBSCRIP_ID),"SUBSCRIPTION_ID"
source="app2.csv" AND ACTION="TO_DELETE","SUBSCRIPTION_ID",
source="app3.csv" AND APP_UNUMBER="U-500","ACCOUNT_ID",
source="app4.csv" AND STATUS="N","CUSTOMER_ID",)

| eval APPLICATION=case(source="app2.csv","APP2",source="app1","APP1")
| chart count over APPLICATION BY SCHEDULED_DELETIONS usenull=f

Issue: When I do chart, the APP1 shows only CUSTOMER_ID count and ignores ACCOUNT_ID and SUBSCRIPTION_ID count. Also I doubt that isnotnull() is of any is here.

Tags (1)
0 Karma

FrankVl
Ultra Champion

Does every event have those 3 fields? A case statement applies the first matching case and never looks at the rest.

Can't you simply do a distinct count on those fields (maybe I'm misunderstanding your end goal here though...)?

index="test" source="app*.csv"
| eval APPLICATION=case(source="app2.csv","APP2",source="app1","APP1") 
| stats dc(CUSTOMER_ID) dc(ACCOUNT_ID) dc(SUBSCRIPTION_ID) by APPLICATION
0 Karma

anantdeshpande
Path Finder

Hi Frank,
Yes. In app1.csv, every event has three fields. As one customer can have multiple accounts, values may be duplicate.

Is there any alternate solution for "A case statement applies the first matching case and never looks at the rest."???

0 Karma

FrankVl
Ultra Champion

That depends on what you want to achieve, which is still a bit unclear to me. My suggestion to do a stats dc() might be an alternative, but it may not be the calculation you're after.

Can you perhaps post some sample data + mockup of the expected outcome? That might clarify a bit more what you are actually trying to calculate.

0 Karma
Get Updates on the Splunk Community!

Splunk Certification Support Alert | Pearson VUE Outage

Splunk Certification holders and candidates!  Please be advised of an upcoming system maintenance period for ...

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...