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!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...