Knowledge Management

How can I improve performance on a query with join?

elzeviske
New Member

In this query I'm joining the same search twice.
I'm looking for every host's top 10 users (in datavolume) and those user's top 10 apps used.

This query is very slow and the subsearch is auto-finalized. How can I improve this query?

host=$field1$ sourcetype=fgt_traffic "user=" service=HTTPS (app=dropbox OR app=slack OR app=trello OR app=hightail OR app=icloud OR app=box OR app=evernote OR app=salesforce OR app=google*docs OR app=google*drive OR appcat="Storage.Backup" OR appcat=Collaboration NOT "Microsoft" NOT "Skype" NOT "OneDrive" NOT "Wikipedia")
| stats sum(BytesOUT_to_Megabytes) as "MB Sent" by user,app
| join user [search sourcetype=fgt_traffic host=$field1$ "user=" service=HTTPS (app=dropbox OR app=slack OR app=trello OR app=hightail OR app=icloud OR app=box OR app=evernote OR app=salesforce OR app=google*docs OR app=google*drive OR appcat="Storage.Backup" OR appcat=Collaboration NOT "Microsoft" NOT "Skype" NOT "OneDrive" NOT "Wikipedia")
| stats sum(BytesOUT_to_Megabytes) as Total by user| sort -Total| head 10 ]
| sort -Total, -"MB Sent"

Tags (1)
0 Karma
1 Solution

asimagu
Builder

you could try something with streamstats maybe?

host=$field1$ sourcetype=fgt_traffic "user=" service=HTTPS (app=dropbox OR app=slack OR app=trello OR app=hightail OR app=icloud OR app=box OR app=evernote OR app=salesforce OR app=google*docs OR app=google*drive OR appcat="Storage.Backup" OR appcat=Collaboration NOT "Microsoft" NOT "Skype" NOT "OneDrive" NOT "Wikipedia") 
| streamstats sum(BytesOUT_to_Megabytes) as Total by user
| stats sum(BytesOUT_to_Megabytes) as "MB Sent" max(Total) as Total by user,app

View solution in original post

0 Karma

asimagu
Builder

you could try something with streamstats maybe?

host=$field1$ sourcetype=fgt_traffic "user=" service=HTTPS (app=dropbox OR app=slack OR app=trello OR app=hightail OR app=icloud OR app=box OR app=evernote OR app=salesforce OR app=google*docs OR app=google*drive OR appcat="Storage.Backup" OR appcat=Collaboration NOT "Microsoft" NOT "Skype" NOT "OneDrive" NOT "Wikipedia") 
| streamstats sum(BytesOUT_to_Megabytes) as Total by user
| stats sum(BytesOUT_to_Megabytes) as "MB Sent" max(Total) as Total by user,app
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 ...