How can I improve performance on a query with join?

elzeviske — Tue, 29 Sep 2020 13:40:05 GMT

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"

Re: How can I improve performance on a query with join?

asimagu — Thu, 13 Apr 2017 11:31:23 GMT

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

topic How can I improve performance on a query with join? in Knowledge Management

How can I improve performance on a query with join?

Re: How can I improve performance on a query with join?