Splunk Search

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

GirolamoBo
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
1 Solution

HiroshiSatoh
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

HiroshiSatoh
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

GirolamoBo
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

HiroshiSatoh
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

GirolamoBo
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...