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
Revered Legend

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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...