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

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

GirolamoBo — Thu, 19 Nov 2015 00:12:15 GMT

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.

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

HiroshiSatoh — Thu, 19 Nov 2015 01:54:52 GMT

Try this!

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

GirolamoBo — Thu, 19 Nov 2015 17:43:54 GMT

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)

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

HiroshiSatoh — Sat, 21 Nov 2015 14:47:15 GMT

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･･･".

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

GirolamoBo — Mon, 23 Nov 2015 20:08:20 GMT

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.