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!

Index This | What did the zero say to the eight?

June 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...

Splunk Observability Cloud's AI Assistant in Action Series: Onboarding New Hires & ...

This is the fifth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...