conditional count(eval)

[learningquery]

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?

BR

[learningquery]

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.

[yuanliu]

---

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

[learningquery]

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

 

 

[learningquery]

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

[yuanliu]

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

[learningquery]

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

 

[learningquery]

absolutely,  I was too beginner to grasp it!

[yuanliu]

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

[richgalloway]

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.

[fredclown]

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.

[fredclown]

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