How can I improve performance on a query with join?

[elzeviske]

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"

[asimagu · Accepted solution]

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