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!

Security Professional: Sharpen Your Defenses with These .conf25 Sessions

Sooooooooooo, guess what. .conf25 is almost here, and if you're on the Security Learning Path, this is your ...

First Steps with Splunk SOAR

Our first step was to gather a list of the playbooks we wanted and to sort them by priority.  Once this list ...

How To Build a Self-Service Observability Practice with Splunk Observability Cloud

If you’ve read our previous post on self-service observability, you already know what it is and why it ...