I have the following data:
A B C Pkg Area Count
NP bcd D02 abc.d PP 1656
NP bcd D05 abc.d PP 870
NP bcd D01 abc.d PP 100
NP cde D05 lmn.o PP 50
NP cde D10 lmn.o PP 350
NP cde D07 lmn.o PP 200
I want to sum the Count column by A and B, but display the resulting rows with the value of Column C which contributed to the maximum Count in the above summation.
So, expected result:
A B C Pkg Area Count
NP bcd D02 abc.d PP 2626
NP cde D10 lmn.o PP 600
Tried the following:
| rex "java\.lang\.(?P[A-Z]+(NP))"
| rex field=_raw "(?\S+)\.[A-Z]\S+\((?<b>\w+)\)"
| search A=NP
...
| stats values(Pkg), values(Area), values(C) , count as Count by A, B, C
| eventstats sum(Count) as Count1 by A, B
| search Area=PP
| sort -Count1
Result:
*A B C Pkg Area Count Count1 *
NP bcd D02 abc.d PP 1656 2626
NP bcd D05 abc.d PP 870 2626
NP bcd D01 abc.d PP 100 2626
NP cde D05 lmn.o PP 50 600
NP cde D10 lmn.o PP 350 600
NP cde D07 lmn.o PP 200 600
If I modify the query as follows:
| rex "java\.lang\.(?P[A-Z]+(NP))"
| rex field=_raw "(?\S+)\.[A-Z]\S+\((?<b>\w+)\)"
| search A=NP
...
| stats values(Pkg), values(Area), values(C) , count as Count by A, B, C
| eventstats sum(Count) as Count1 by A, B first(Count1) as Top_Count by C
| search Area=PP
| sort -Count1
..the Top_Count column shows up empty:
*A B C Pkg Area Count Top_Count *
NP bcd D02 abc.d PP 1656
NP bcd D05 abc.d PP 870
NP bcd D01 abc.d PP 100
NP cde D05 lmn.o PP 50
NP cde D10 lmn.o PP 350
NP cde D07 lmn.o PP 200
Removing the Count column at the end..
| rex "java\.lang\.(?P[A-Z]+(NP))"
| rex field=_raw "(?\S+)\.[A-Z]\S+\((?<b>\w+)\)"
| search A=NP
...
| stats values(Pkg), values(Area), values(C) , count as Count by A, B, C
| eventstats sum(Count) as Count1 by A, B first(Count1) as Top_Count by C
| search Area=PP
| sort -Count1
| fields - Count
..hides all the Count columns:
*A B C Pkg Area Top_Count *
NP bcd D02 abc.d PP
NP bcd D05 abc.d PP
NP bcd D01 abc.d PP
NP cde D05 lmn.o PP
NP cde D10 lmn.o PP
NP cde D07 lmn.o PP
I am not sure why all the Count columns get removed considering the Fields - command is applied only at the very end.
Any pointers? Thank you!
... View more