Archive

How can I improve performance on a query with join?

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=fgttraffic "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=googledocs OR app=googledrive OR appcat="Storage.Backup" OR appcat=Collaboration NOT "Microsoft" NOT "Skype" NOT "OneDrive" NOT "Wikipedia")
| stats sum(BytesOUT
toMegabytes) 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=googledocs OR app=googledrive OR appcat="Storage.Backup" OR appcat=Collaboration NOT "Microsoft" NOT "Skype" NOT "OneDrive" NOT "Wikipedia")
| stats sum(BytesOUTtoMegabytes) as Total by user| sort -Total| head 10 ]
| sort -Total, -"MB Sent"

Tags (1)
0 Karma
1 Solution

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

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