topic Re: Splunk case statement to count values based on field name in Splunk Search

Splunk case statement to count values based on field name

anantdeshpande — Tue, 29 Sep 2020 19:45:11 GMT

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.

Re: Splunk case statement to count values based on field name

FrankVl — Fri, 01 Jun 2018 12:33:12 GMT

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

Re: Splunk case statement to count values based on field name

anantdeshpande — Fri, 01 Jun 2018 13:17:08 GMT

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."???

Re: Splunk case statement to count values based on field name

FrankVl — Fri, 01 Jun 2018 13:41:35 GMT

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.