Archive

Splunk case statement to count values based on field name

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!