topic Re: Cannot get count by case statement in Splunk Search

Cannot get count by case statement

tjsnow — Fri, 08 Nov 2024 15:11:46 GMT

I am trying to simply break down a url to extract the region and chart the use of specific urls over time. but i just get a NULL count of everything. How do i display the counts as separate values?

[query] | eval region=case(url like "%region1%","Region 1",url like "%region2%","Region 2") | timechart span=1h count by region

Re: Cannot get count by case statement

johnhuang — Fri, 08 Nov 2024 03:13:39 GMT

You can use LIKE or MATCH

| eval region=CASE(LIKE(url, "%region1%"), "Region 1", LIKE(url, "%region2%"), "Region 2") | eval region=CASE(MATCH(url, "region1"), "Region 1", MATCH(url, "region2"), "Region 2")

Re: Cannot get count by case statement

yuanliu — Fri, 08 Nov 2024 06:22:46 GMT

Going back to my four commandments of asking answerable questions:

Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search (SPL that volunteers here do not have to look at).
Illustrate the desired output from illustrated data.
Explain the logic between illustrated data and desired output without SPL.
If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.

Until you can illustrate your data, no one can help you. On the surface, your case function should work given this set of data:

url

abc.fromregion1.com

def.toregion2wego.com

ghi.fromregion1toregion2.com

You can run a stats and get

region	count
Region 1	2
Region 2	1

Here is the emulation to prove the above.

| makeresults format=csv data="url abc.fromregion1.com def.toregion2wego.com ghi.fromregion1toregion2.com" ``` data emulation above ``` | eval region=case(url like "%region1%","Region 1",url like "%region2%","Region 2") | stats count by region