Splunk Search

How do you represent SQL case and sum/count in a search query?

tommyp1972
New Member

I am using the Splunk web service to get data that was previously gotten from a database table that stored log information. I have no idea how t hey got the info into the databases. However, I am trying to replicate it.

I cannot find a way to get the sum and count when the "fault" field" is "N" or "y". Can anyone help? Here is the SQL I am trying to translate:

select
name,
sum(case when fault = 'N' then 1 else 0 end) as pass,
sum(case when fault = 'Y' then 1 else 0 end) as fail,
count(*) total,
. . . . .

Tags (1)
0 Karma

tommyp1972
New Member

I don't know. But I will surely try it and post my findings here. THanks for the reply.

0 Karma

tommyp1972
New Member

OK. I checked. That didn't work. The count worked for the case part (pass and fail) didn't work. I swear I searched the documentation and until just now I found the eval/where and there is a case. The case I found yesterday was for case insensitivity/sensitivity for strings. Anyway, I tried this but I get 0's for the pass and fail but I know that is wrong as the total has a number greater than 0.

count(eval Pass=case(fault==Y,1)), count(eval Fail=case(fault==N,1)),

0 Karma

Ayn
Legend

I find that it's often easier to state the GOAL rather than how you would achieve it in some other language. While Splunk's search language can sometimes be similar to how SQL handles things it's often harder to make direct translations between the two than independently coming up with a solution in either language to the problem.

Is this something like what you're looking for?

... | stats count(eval(fault=="N")) as pass, count(eval(fault=="Y")) as fail, count as total
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...