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