Dashboards & Visualizations

count unique values and visualize in pie chart

sarit_s
Communicator

Hello
I have this query :

index="bigfixreport"  | timechart count(Category__Names_of_Patches) as totalNumberOfPatches by Computer_Name
| eval exposure_level = case(
    totalNumberOfPatches >= 2 AND totalNumberOfPatches <= 5, "Low Exposure",
    totalNumberOfPatches >= 6 AND totalNumberOfPatches <= 9, "Medium Exposure", 
    totalNumberOfPatches >= 10, "High Exposure", 
    totalNumberOfPatches == 1, "Compliant",
    1=1, "<not reported>"
  )

| eval category=exposure_level
| xyseries category exposure_level totalNumberOfPatches

 

The purpose of this query is to count the number of patches for each computer name and visualize it in pie chart - one for each category and color each pie in different color ("Low Exposure" - blue, "Medium Exposure" - yellow, "High Exposure" - red, "Compliant" - green, <not reported> - gray)

I have few problems
1. since i count numbers, <not reported> not count and does not display in the list

2. i have new file every day and it is possible the for few day the number of patches for some computer will be the same (for example, it will be 3 patches for specific computer for 5 days)
if i just count the number of patches it will count 3+3+3+3+3 and it is not true since its the same 3 patches

Labels (3)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

You can't do this construct

| timechart count as totalNumberOfPatches by X
| eval a=case(X=bla, 1...)

because when you split a timechart by a field, the count AS totalNumberOfPatches does not result in a field called totalNumberOfPatches, but the fields are the names of the values of  the split by clause, in your case Computer_Name.

I assume you have a stats rather than timechart, but as @richgalloway says, using dc() is the way to count distinct versions of a field.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

1. Finding something that is not there is not Splunk's strong suit.  See this blog entry for a good write-up on it.

https://www.duanewaddle.com/proving-a-negative/

2. Use the distinct_count function instead of count to get the number of unique patches.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

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 ...