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

[sudheeraha]

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

[woodcock]

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

[somesoni2]

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]

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