SOLVED

Custom mass download alert

%3CLINGO-SUB%20id%3D%22lingo-sub-2798022%22%20slang%3D%22en-US%22%3ECustom%20mass%20download%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798022%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%20I%20have%20been%20messing%20around%20with%20Cloud%20App%20Security%20and%20have%20noticed%20their%20mass%20download%20alert%2C%20unfortunately%20i%20seem%20unable%20to%20add%20exclusion%20to%20this%20alert%20so%20it%20triggers%20way%20to%20often%20on%20totally%20uninportant%20sharepoint%20sites.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETherefore%20i%20have%20made%20my%20own%20query%20to%20check%20for%20mass%20downloads%2C%20however%20i%20can't%20make%20the%20query%20both%20count%20how%20many%20download%20operations%20a%20user%20has%20togheter%20with%20which%20sites%20they%20have%20downloaded%20from.%20It's%20either%20how%20many%20downloads%20total%20and%20no%20info%20on%20which%20site%20they%20have%20downloaded%20from%20or%20on%20a%20per%20sharepoint-site%20basis%20which%20is%20not%20very%20usefull%20when%20some%20of%20the%20folders%20are%20very%20small%20and%20will%20not%20trigger%20on%20the%20set%20threshold.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20query%20looks%20like%20this%20where%20i%20have%20used%20the%20extract%20function%20to%20filter%20out%20the%20uninteresting%20sharepoint%20sites%20which%20the%20CAS%20alerts%20keep%20triggering%20on.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Elet%20uninterestingPNNNNSites%20%3D%20OfficeActivity%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2FRemoves%20sites%20containing%20%2Fp-NNNN%2C%20N%20being%20a%20number%0A%7C%20where%20Operation%20contains%20%22download%22%20%0A%7C%20extend%20pGroups%20%3D%20extract(%22(p%2B%5C%5C-%2B%5C%5Cd%7B4%7D%5C%5C%2F%24)%22%2C1%2C%20Site_Url)%0A%7C%20where%20pGroups%20!%3D%20%22%22%20%0A%7C%20summarize%20count()%20by%20Site_Url%3B%0Alet%20uninterestingPersonalSites%20%3D%20OfficeActivity%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2FRemoves%20%2Fpersonal%20sites%0A%7C%20where%20Operation%20contains%20%22download%22%0A%7C%20extend%20personalGroups%20%3D%20extract(%22(%5C%5C%2F%2Bpersonal%2B%5C%5C%2F)%22%2C%201%20%2C%20Site_Url)%20%0A%7C%20where%20personalGroups%20!%3D%20%22%22%0A%7C%20summarize%20count()%20by%20Site_Url%3B%0Alet%20uninterestingSiteP%20%3D%20OfficeActivity%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2FRemoves%20the%20site%20%2Fp%2F%2C%20this%20being%20an%20old%20site%20that%20is%20not%20going%20to%20be%20used.%20%0A%7C%20where%20Operation%20contains%20%22download%22%20%0A%7C%20extend%20pGroups%20%3D%20extract(%22(%2Fp%2F)%22%2C1%2C%20Site_Url)%0A%7C%20where%20pGroups%20!%3D%20%22%22%20%0A%7C%20summarize%20count()%20by%20Site_Url%3B%0AOfficeActivity%20%0A%7C%20where%20Operation%20contains%20%22download%22%20%0A%7C%20where%20Site_Url%20!in%20(%20uninterestingPersonalSites%20)%20%0A%7C%20where%20Site_Url%20!in%20(%20uninterestingPNNNNSites)%0A%7C%20where%20Site_Url%20!in%20(%20uninterestingSiteP)%0A%7C%20summarize%20count()%20by%20Site_Url%2C%20UserId%2C%20ClientIP%20%20%20%20%2F%2FRemove%20Site-Url%20for%20total%20downloads%20per%20user%0A%7C%20project-rename%20Number_of_downloadoperations%20%3D%20count_%0A%7C%20where%20Number_of_downloadoperations%20%26gt%3B%20300%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BPreferably%20i%20would%20be%20able%20to%20summarize%20by%20only%20UserId%20and%20ClientIP%20giving%20a%20count%20for%20how%20many%20downloads%20they%20have%20done%20in%20a%20day%2C%20but%20also%20attaching%20a%20list%20of%20which%20sites%20they%20have%20downloaded%20from%20for%20analysts%20to%20act%20on%20without%20having%20to%20run%20their%20own%20manual%20search.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2804706%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20mass%20download%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804706%22%20slang%3D%22en-US%22%3EAt%20the%20end%20would%20something%20like%3CBR%20%2F%3E%3CBR%20%2F%3E%7C%20summarize%20make_set(UserId)%20by%20ClientIP%2CSite_Url%2CNumber_of_downloadoperations%3CBR%20%2F%3E%3CBR%20%2F%3EGive%20you%20what%20you%20are%20after%3F%3C%2FLINGO-BODY%3E
Contributor

Greetings, I have been messing around with Cloud App Security and have noticed their mass download alert, unfortunately i seem unable to add exclusion to this alert so it triggers way to often on totally uninportant sharepoint sites.

 

Therefore i have made my own query to check for mass downloads, however i can't make the query both count how many download operations a user has togheter with which sites they have downloaded from. It's either how many downloads total and no info on which site they have downloaded from or on a per sharepoint-site basis which is not very usefull when some of the folders are very small and will not trigger on the set threshold. 

 

My query looks like this where i have used the extract function to filter out the uninteresting sharepoint sites which the CAS alerts keep triggering on.

let uninterestingPNNNNSites = OfficeActivity                //Removes sites containing /p-NNNN, N being a number
| where Operation contains "download" 
| extend pGroups = extract("(p+\\-+\\d{4}\\/$)",1, Site_Url)
| where pGroups != "" 
| summarize count() by Site_Url;
let uninterestingPersonalSites = OfficeActivity             //Removes /personal sites
| where Operation contains "download"
| extend personalGroups = extract("(\\/+personal+\\/)", 1 , Site_Url) 
| where personalGroups != ""
| summarize count() by Site_Url;
let uninterestingSiteP = OfficeActivity                //Removes the site /p/, this being an old site that is not going to be used. 
| where Operation contains "download" 
| extend pGroups = extract("(/p/)",1, Site_Url)
| where pGroups != "" 
| summarize count() by Site_Url;
OfficeActivity 
| where Operation contains "download" 
| where Site_Url !in ( uninterestingPersonalSites ) 
| where Site_Url !in ( uninterestingPNNNNSites)
| where Site_Url !in ( uninterestingSiteP)
| summarize count() by Site_Url, UserId, ClientIP    //Remove Site-Url for total downloads per user
| project-rename Number_of_downloadoperations = count_
| where Number_of_downloadoperations > 300

 Preferably i would be able to summarize by only UserId and ClientIP giving a count for how many downloads they have done in a day, but also attaching a list of which sites they have downloaded from for analysts to act on without having to run their own manual search. 

2 Replies
best response confirmed by stianhoydal (Contributor)
Solution
At the end would something like

| summarize make_set(UserId) by ClientIP,Site_Url,Number_of_downloadoperations

Give you what you are after?
Perfect, thanks again Matthew :)