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)
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
|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
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)
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)