How to show distinct count?

[harsush]

index=XX sourcetype=YY source=*/log/abc.log
| dedup _time, bppm_message, bppm_nodename sortby -_indextime
| rex field=bppm_operations_annotations "0x[a-z0-9]{8},(?<onepass>\w+),,(OPERATOR|OVERRIDE)_CLOSED"
| rex field=bppm_operations_annotations "(OPERATOR_CLOSED:|OVERRIDE_CLOSED:|OWNERSHIP_TAKEN:)\s+(?<closed_with>(\w|\s|\-|\/|\.|\[|\])+),"
| rex field=bppm_annotations "0x[a-z0-9]{8},[a-zA-Z0-9]+,(?<INC_CREATED_TSIM>.*)"
| rex field=bppm_annotations "[0-9]{3}[A-Z0-9]{5},[a-zA-Z0-9]+,(?<MUTIPLE_CLOSURE_ANNOTATION>.*)"
| rex field=bppm_annotations "0x[a-z0-9]{8},CME-remedy.mrl:execute AddIncidentToNotes,Incident (?<INC_CREATED_TSIM_2>(\w|\s|\-|\/|\.|\[|\])+) created by"
| eval bppm_nar_close_multiple_events=if(NOT match(bppm_operations_annotations,"OVERRIDE_CLOSED") AND (NOT match(bppm_operations_annotations,"OPERATOR_CLOSED")),"yes", "no")
| eval closed_with = if(isnull(closed_with) OR closed_with="Null", INC_CREATED_TSIM, closed_with)
| eval closed_with = if(isnull(closed_with) OR closed_with="Null", INC_CREATED_TSIM_2, closed_with)
| eval closed_with = if(isnull(closed_with) OR closed_with="Null", MUTIPLE_CLOSURE_ANNOTATION, closed_with)
| fillnull value="Null" closed_with
| eval time=strftime(_time,"%x-%H:%M:%S")
| lookup onepasslk onepass
| search bppm_ecdb_env="***"
| fillnull value="Null"
| stats count(bppm_message) as Total_count, count(eval(like(closed_with, "%INC%"))) as Closed-With-INC, count(eval(like(closed_with, "%CRQ%"))) as Closed-With-CRQ, count(eval(like(closed_with, "%PKE%"))) as Closed-With-PKE, count(eval(like(closed_with, "%WO%"))) as Closed-With-WO by username
| sort -Total_count

Abv query iam able to pull data as shown in the attached image but i want add 3 more columns Unique INC Count , Unique CRQ Count , Unique WO Count which show distinct count

pls help on how to achieve this

[harsush]

i tried abv already using distinct_count it will give you one -- if i separately use distinct_count it will show me the result -- bu my requirement is to show how many alerts closed using INC and also how many distinct INC's are used to close those alerts

example :
X closed 70 alerts

Out of 70 X closed 30 alerts with INC in annotation field

30 alerts with INC in annotation field he used 2 INC1 & INC2

below is what output should look like

username	Total_count	Closed-With-INC	Distinct INC count	Closed-With-CRQ	Distinct CRQ count	Closed-With-PKE	Distinct WO PKE	Closed-With-WO	Distinct WO count
X	70	30	2	20	1	2	1	18	1

distinct_count(eval(like(closed_with, "%INC%"))) as distinct_INC will give count as 1 where we have multiple INC's in it

[yuanliu]

Do you mean

| stats count(bppm_message) as Total_count, count(eval(like(closed_with, "%INC%"))) as Closed-With-INC, distinct_count(eval(if(like(closed_with, "%INC%"), closed_with, null()))) as distinct_INC count(eval(if(like(closed_with, "%CRQ%"), closed_with, null()))) as Closed-With-CRQ, distinct_count(if(eval(like(closed_with, "%CRQ%"), closed_with, null()))) as distinct_CRQ count(eval(like(closed_with, "%PKE%"))) as Closed-With-PKE, count(eval(like(closed_with, "%WO%"))) as Closed-With-WO distinct_count(if(eval(like(closed_with, "%WO%"), closed_with, null()))) as distinct_WO by username

Again, this is a very messy expression. In fact, the above is going to give you one extra count because null is a distinct value. It is best to clean up the evaluation logic before going into stats.

[yuanliu]

Have you looked up distinct_count(X) or dc(X)?

| stats count(bppm_message) as Total_count, count(eval(like(closed_with, "%INC%"))) as Closed-With-INC, distinct_count(eval(like(closed_with, "%INC%"))) as distinct_INC count(eval(like(closed_with, "%CRQ%"))) as Closed-With-CRQ, distinct_count(eval(like(closed_with, "%CRQ%"))) as distinct_CRQ count(eval(like(closed_with, "%PKE%"))) as Closed-With-PKE, count(eval(like(closed_with, "%WO%"))) as Closed-With-WO distinct_count(eval(like(closed_with, "%WO%"))) as distinct_WO by username

Improvements to consider: Using complex evals in stats reduces readability for future maintenance. Consider place them in variables. Specifically, as you are doing both count and dc on the same value, storing them in variables reduces cost, too.