topic Re: conditional count(eval) in Splunk Search

conditional count(eval)

learningquery — Tue, 17 Oct 2023 18:03:33 GMT

Hi community,

| eval ycw = strftime(_time, "%Y_%U")
| stats count(eval("FieldA"="True")) as FieldA_True,
count(eval('FieldB'="True")) as FieldB_True,
count(eval('FieldC'="True")) as FieldC_True
by ycw
| table ycw, FieldA_True, FieldB_True, FieldC_True

I get 0 result even though there is data. Could anyone please suggest a correct query?

Re: conditional count(eval)

fredclown — Tue, 17 Oct 2023 19:06:44 GMT

I've added some fake data before the SPL you provided and when I run it I get results like the below screenshot. It's hard to say what is going on in your environment and with your data but I would tend to think either the base search returned nothing or your fields (FieldA, FieldB, FieldC) don't exist in the data returned from the base search.

| makeresults count=300 | eval _time=_time-(86400*(random() % 61)) | eval FieldA=if(random() % 2==1,"True", "False") | eval FieldB=if(random() % 2==1,"True", "False") | eval FieldC=if(random() % 2==1,"True", "False") ```^^^^ Fake data added by me ^^^^``` | eval ycw = strftime(_time, "%Y_%U") | stats count(eval('FieldA'="True")) as FieldA_True, count(eval('FieldB'="True")) as FieldB_True, count(eval('FieldC'="True")) as FieldC_True by ycw | table ycw, FieldA_True, FieldB_True, FieldC_True

Re: conditional count(eval)

richgalloway — Tue, 17 Oct 2023 19:05:56 GMT

Confirm the ycw field has values. If ycw is null then the stats command will return no results - because there are no values by which to group the stats.

Also, the evals should not have the field names in double quotes because that treats them a literal strings rather than as field names. Use single quotes or no quotes around field names.

Re: conditional count(eval)

fredclown — Wed, 18 Oct 2023 16:58:26 GMT

Based on the search given ycw will always have a value as it is derived from _time and every event has an _time.

The second part is correct. I too noticed that and corrected the double quotes in the eval in my answer.

Re: conditional count(eval)

learningquery — Fri, 20 Oct 2023 09:26:02 GMT

Thanks for providing the hints. I tried both the changes eval and random command it did not work unfortunately. While I am sure the data exists.

Re: conditional count(eval)

yuanliu — Sat, 21 Oct 2023 08:50:02 GMT

unfortunately. While I am sure the data exists.

By "data exists" do you mean some values of field "Infra Finding" are string "Yes", etc.? Can you show sample output of

earliest=-10w latest=now LOB=HEC search_name!=null | eval ycw = strftime(_time, "%Y_%U") | fields ycw "Infra Finding" "OS Finding" "App Finding" | stats values(*) as * by ycw

Re: conditional count(eval)

learningquery — Sat, 21 Oct 2023 19:23:22 GMT

Thanks for the hint. Attached is the result I get. But want the total count of all TRUE cases listed per calendar week in numbers and also in % (I don't want the FALSE as result ). The attached .xls shows what I am looking for with example numbers

Re: conditional count(eval)

learningquery — Sat, 21 Oct 2023 19:27:59 GMT

Sorry, now I put the correct result (Pl ignore the previous result)

Re: conditional count(eval)

yuanliu — Sat, 21 Oct 2023 20:38:42 GMT

Pro tip: Post data and output in text. It is much easier for volunteers.

So, the fields do NOT have values "True" as your mock code has implied. They have values "true". If you haven't grasped this, Splunk stores most data in string tokenized strings and numeric values. Have you tried this?

| eval ycw = strftime(_time, "%Y_%U") | stats count(eval('FieldA'="true")) as FieldA_True, count(eval('FieldB'="true")) as FieldB_True, count(eval('FieldC'="true")) as FieldC_True by ycw | table ycw, FieldA_True, FieldB_True, FieldC_True

Re: conditional count(eval)

learningquery — Fri, 27 Oct 2023 06:20:34 GMT

I figured out the minor error was - "True" needs to be "true", as the value returns a boolean.

Re: conditional count(eval)

yuanliu — Fri, 27 Oct 2023 06:23:56 GMT

Isn't this exactly what I posted in https://community.splunk.com/t5/Splunk-Search/conditional-count-eval/m-p/665764/highlight/true#M228420? Splunk doesn't really store boolean values.

Re: conditional count(eval)

learningquery — Fri, 27 Oct 2023 06:36:40 GMT

absolutely, I was too beginner to grasp it!