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!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

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

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...