How to edit my search to get a conditional distinct count "stats dc(field)" for a pie chart?

[bowesmana]

I have events with a type and an id. The id field can be null or a number where that number may repeat, e.g.

type,id
1,NULL
1,NULL
1,NULL
3,1234
3.1234
1,2222
1.2222
1.2222
5,555
5,555
5,555
7,999
7,999
8,NULL

I want to get a pie chart, which means I need a single column of results and I want to display 4 values

type <= 3 (= class 0)
type >3 (= class 1)

 | eval class=case(type <=3, 0, type > 3, 1) | stats count(eval(id=="NULL")) as Singles dc(id) as Multis by class | eval Class0Singles=case(class=0,Singles), Class1Singles=case(class=1,Singles), Class0Multis=case(class=0,Multis), Class1Multis=case(class=1,Multis) | fields - class Singles Multis | eval id = 1 | untable id Metric Value | fields - id

The above gives me a table like this

Class0Multis     2 (rows 4-8)
Class0Singles   3 (first 3 rows)
Class1Multis     2 (rows 9-13)
Class1Singles   1 (row 14)

which gets me the pie chart, but it seems rather cumbersome. I could do two counts without doing the "by class" to get the singles (id="NULL"), but the problem seems to be that it's not possible to dc(id) with a 'where class=1'.

this is to produce a pie chart

Is there a way to make this simpler?

[maciep · Accepted solution]

I'm not sure there is anything really wrong with a roundabout approach like that. Sometimes with Splunk, what seems simple might take a little work to get there. The nice part is that you can almost always get there!

That said, here is another way (I think). It's a bit cumbersome as well, but just a different approach. The main hurdle is that you need the distinct count for multis. But instead of stats maybe a dedup will work.

| eval class=case(type <=3, 0, type > 3, 1) 
| eval id = if(id="NULL",null,id) 
| dedup keepempty=t class id 
| eval metric = case (class=0 AND isnull(id),"Class0Singles",class=1 AND isnull(id),"Class1Singles",class=0 AND isnotnull(id),"Class0Multis",class=1 AND isnotnull(id),"Class1Multis") 
| stats count by metric

So still eval the class field, but also set the id to literally null if it's "NULL". Then dedup on the class and id but keep those null fields around. At this point you should have events that are unique to class and id if id isn't null (multis)..and then all of the events if id is null (singles)

At that point, do an eval on each event to create the metric field. And finally count those by that field. You should still be getting a count all singles by class and only have a count of unique ids for each class.

I think that's right? Again, still a bit ugly even if it does work, but just a different trip to the same destination.

[bowesmana]

Thanks for that and yes, that works. In fact it highlighted a problem with the original stats as the dc(id) will include the id="NULL" as an extra, so my Multi counts were 1 too many. Your approach gets the numbers right.

It's always good to see different ways of doing stuff with Splunk as there are so many ways to skin the cat.

I'm not a great fan of dedup as it can be pretty heavy, but doing it only on those fields I guess has to be pretty much what a dc() has to do anyway.

[woodcock]

Assuming that your faked output has a mistake ( Class0Singles should be 1 ), like this:

| eval class= "Class" . if(type <=3, "0", "1") . if(id=="NULL", "Singles", "Multis")
| stats dc(id) BY class

[bowesmana]

Thanks for the comments. The output is correct, from my query you can see that I want count of id=NULL to represent Singles, but dc(id) to represent Multis. The stats command

stats count(eval(id=="NULL")) as Singles dc(id) as Multis by class

is doing what I want to get the numbers, but it gives me a two row table with 3 columns, i.e.

class Singles Mutis
0 3 1
1 1 2

but that's no good for a pie chart, which cannot display data from the second value column, so I have to transpose the Multis column into new rows and the differentiate the class column, which is where the eval and untable commands do their stuff.

I was really just wondering if there was a single way to get the 4 required rows for the pie chart with column 1 having the correct labels with column 2 having the corresponding values

[Ravan]

Can you try with below case statement ?

| eval result=case(type<=3 AND id="NULL","Class0Singles",type<=3 AND id!="NULL","Class0Multis",type>3 AND id="NULL","Class1Singles",type>3 AND id!="NULL","Class1Multis")|stats count by result

[bowesmana]

It's not 'count' I'm after it's distinct_count() for id!=NULL