Splunk Search

How to show distinct count?

harsush
Path Finder

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_0-1669893274767.png

Labels (2)
0 Karma

harsush
Path Finder

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

usernameTotal_countClosed-With-INCDistinct INC countClosed-With-CRQDistinct CRQ countClosed-With-PKEDistinct WO PKEClosed-With-WODistinct WO count
X7030220121181


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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...