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 Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...