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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...