Splunk Search

How to create a search to get count total with percentage against total count?

sudheeraha
Engager

Hi there,

I have below result with this query.

index="abc" 
Properties.CorrelationId != XYZ 
| stats count by Properties.CorrelationId 
| sort - count 
| eventstats sum(count) as totalCount 
| eval percentage=((count/totalCount)*100)

Result:

Properties.CorrelationId                                             count       percentage totalCount
23F4991E-EB37-447A-6702-44B7834DA0E2          7     63.63                  33
A8D81A89-2D6A-48AD-733B-CD0A802F62B8          7     63.63                  33
D85CB087-6BE9-419E-670A-BD9770525A15         7              63.63                  33
1200CC97-6615-4AF4-7586-DC00207AB1E8         6              36.36                  33
18F8F6C7-752A-42DB-5880-ABE0BF8E5DE2         6              36.36                  33

But what I after is below result. without the Properties.CorrelationId column

Count   Total     percentage   Grand Total
7             3                   63.63               33
6             2                    36.36               33
Tags (2)
0 Karma

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="Properties.CorrelationId=23F4991E-EB37-447A-6702-44B7834DA0E2,count=7,percentage=63.63,totalCount=33 Properties.CorrelationId=A8D81A89-2D6A-48AD-733B-CD0A802F62B8,count=7,percentage=63.63,totalCount=33 Properties.CorrelationId=D85CB087-6BE9-419E-670A-BD9770525A15,count=7,percentage=63.63,totalCount=33 Properties.CorrelationId=1200CC97-6615-4AF4-7586-DC00207AB1E8,count=6,percentage=36.36,totalCount=33 Properties.CorrelationId=18F8F6C7-752A-42DB-5880-ABE0BF8E5DE2,count=6,percentage=36.36,totalCount=33"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv
| table P* count per* tot*

| rename COMMENT AS "Everything above generates sample events; everything below is your solution"

| stats count AS "Total" BY count percentage totalCount
| table count Total percentage totalCount
| rename totalCount AS "Grand Total", count AS Count
| sort 0 - percentage
0 Karma

somesoni2
SplunkTrust
SplunkTrust

The values in your output doesn't seem to match the query you wrote (percentage calculation). Give this a try:

index="abc" Properties.CorrelationId != XYZ 
| stats count as Count by Properties.CorrelationId
| eventstats sum(Count) as GrandTotal
| stats count as Total values(GrandTotal) as GrandTotal by Count
| eval percentage=((count/GrandTotal)*100)

sudheeraha
Engager

Thanks for your reply. It worked to some extend. Apparently cannot see the out put column for the last statement )"| eval percentage=((count/GrandTotal)*100)".

Further how can I add the time stamp range column as well to figure out the time period. Basically what time of the day these records created. (perhaps time range not exact time)

Thanks

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...