Splunk Search
Highlighted

How do I edit my search with appendpipe and subsearch to append subtotals under each individual user, not at the end?

Explorer

Here is my search:

sourcetype="xyz" [search sourcetype="abc" "Threshold exceeded"| top user limit=3 | fields user] 
| stats count by user integration 
| appendpipe [stats sum(count) by user integration | eval user="Total".user."'s count" ] 
| sort count

It returns correct stats, but the subtotals per user are not appended to individual user's, but appended to the end.

I currently get this:

userA integration1 count
userA integration2 count
userB integration3 count
userB integration4 count
Total userA's sum(count)
Total userB's sum(count)

I would like the totals per user to be grouped like this:

userA integration1 count
userA integration2 count
Total userA's sum(count)
userB integration3 count
userB integration4 count
Total userB's sum(count)

Thank you very much.

0 Karma
Highlighted

Re: How do I edit my search with appendpipe and subsearch to append subtotals under each individual user, not at the end?

Champion

Try this!

sourcetype="xyz" [search sourcetype="abc" "Threshold exceeded"| top user limit=3 | fields user]
| stats count by user integration
| appendpipe [stats sum(count) by user | eval user=user."'s Total count" ]
| sort user count

View solution in original post

Highlighted

Re: How do I edit my search with appendpipe and subsearch to append subtotals under each individual user, not at the end?

Explorer

thank you HiroshiStosh. From what I can see you added user to the sort. I am afraid it did not work: the totals are still listed together, but they are now on top:
Total userA's sum(count)
Total userB's sum(count)
userA integration1 count
userA integration2 count
userB integration3 count
userB integration4 count
I would like each row for totals for respective user to appear after the rows for each user:
userA integration1 count
userA integration2 count
Total userA's sum(count)
userB integration3 count
userB integration4 count
Total userB's sum(count)

0 Karma
Highlighted

Re: How do I edit my search with appendpipe and subsearch to append subtotals under each individual user, not at the end?

Champion

It is an execution result of my search statement.
user,integration,count,sum(count)
userA,integration1,10,
userA,integration2,99,
userA's Total count,,,109

userB,integration4,11,
userB,integration3,80,
userB's Total count,,,90

And is the result of running your search statement.

user,integration,count,sum(count)
Total userA's count,,,109 ※count is null
Total userB's count,,,90 ※count is null
userA,integration1,10,
userB,integration4,11,
userB,integration3,80,
userA,integration2,99,

It is sufficient to add a field for sorting If you want to field name "Total user・・・".

0 Karma
Highlighted

Re: How do I edit my search with appendpipe and subsearch to append subtotals under each individual user, not at the end?

Explorer

Thank you! I missed one of the changes you made. This is what I missed the first time I tried your suggestion:

| eval user=user."'s Total count"

I left the string "Total" in front of user:

| eval user="Total".user."'s count"

After I removed "Total" as it's in your search, the total lines printed correctly. Would you please explain why "Total" concatenated with user caused the issue? Thanks again for the help.

0 Karma