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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...