cloudflare/Azure-Sentinel

// Name: Disabled accounts using squid proxy
//
// Id: 959fe0f0-7ac0-467c-944f-5b8c6fdc9e72
//
// Description: Look for accounts that have a been recorded as disabled by AD in the previous week but are still using the proxy during 
// the current week. This query presumes the default squid log format is being used.
//
// DataConnector: #Syslog; DataTypes: #Squid
//
// Tactics: #Discovery, #CommandAndControl
//
let disabledAccounts = (){
SigninLogs 
| where TimeGenerated between(ago(14d) .. ago(7d))
| where ResultType == "50057" 
| where ResultDescription == "User account is disabled. The account has been disabled by an administrator." 
};
let proxyEvents = (){
Syslog
| where TimeGenerated > ago(7d)
| where ProcessName contains "squid"
| extend URL = extract("(([A-Z]+ [a-z]{4,5}:\\/\\/)|[A-Z]+ )([^ :]*)",3,SyslogMessage), 
         SourceIP = extract("([0-9]+ )(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3}))",2,SyslogMessage), 
         Status = extract("(TCP_(([A-Z]+)(_[A-Z]+)*)|UDP_(([A-Z]+)(_[A-Z]+)*))",1,SyslogMessage), 
         HTTP_Status_Code = extract("(TCP_(([A-Z]+)(_[A-Z]+)*)|UDP_(([A-Z]+)(_[A-Z]+)*))/([0-9]{3})",8,SyslogMessage),
         User = extract("(CONNECT |GET )([^ ]* )([^ ]+)",3,SyslogMessage),
         RemotePort = extract("(CONNECT |GET )([^ ]*)(:)([0-9]*)",4,SyslogMessage),
         Domain = extract("(([A-Z]+ [a-z]{4,5}:\\/\\/)|[A-Z]+ )([^ :\\/]*)",3,SyslogMessage),
         Bytes = toint(extract("([A-Z]+\\/[0-9]{3} )([0-9]+)",2,SyslogMessage)),
         contentType = extract("([a-z/]+$)",1,SyslogMessage)
| extend TLD = extract("\\.[a-z]*$",0,Domain)
};
proxyEvents 
| where Status !contains 'DENIED'
| join kind=inner disabledAccounts on $left.User == $right.UserPrincipalName
| extend AccountCustomEntity = UserPrincipalName