Splunk ESCU

let ImagesList = dynamic (["sethc.exe","utilman.exe","osk.exe","Magnify.exe","Narrator.exe","DisplaySwitch.exe","AtBroker.exe"]); 
let OriginalFileNameList = dynamic (["sethc.exe","utilman.exe","osk.exe","Magnify.exe","Narrator.exe","DisplaySwitch.exe","AtBroker.exe","SR.exe","utilman2.exe","ScreenMagnifier.exe"]); 
Event
| where EventLog == "Microsoft-Windows-Sysmon/Operational" and EventID==1
| parse EventData with * 'ProcessId">' ProcessId "<" * 'Image">' Image "<" * 'OriginalFileName">' OriginalFileName "<" *
| where Image has_any (ImagesList) and not (OriginalFileName has_any (OriginalFileNameList))
| parse EventData with * 'ProcessGuid">' ProcessGuid "<" * 'Description">' Description "<" * 'CommandLine">' CommandLine "<" * 'CurrentDirectory">' CurrentDirectory "<" * 'User">' User "<" * 'LogonGuid">' LogonGuid "<" * 'Hashes">' Hashes "<" * 'ParentProcessGuid">' ParentProcessGuid "<" * 'ParentImage">' ParentImage "<" * 'ParentCommandLine">' ParentCommandLine "<" * 'ParentUser">' ParentUser "<" *
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by EventID, Computer, User, ParentImage, ParentProcessGuid, ParentCommandLine, ParentUser, Image, ProcessId, ProcessGuid, CommandLine, Description, OriginalFileName, CurrentDirectory, Hashes
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend AccountName = tostring(split(User, "\\")[1]), AccountNTDomain = tostring(split(User, "\\")[0])
| extend ImageFileName = tostring(split(Image, "\\")[-1])
| extend ImageDirectory = replace_string(Image, ImageFileName, "")
| project-away DomainIndex

let selfServicePasswordReset = dynamic(["Self-service password reset flow activity progress", "Change password (self-service)", "Reset password (self-service)"]); 
//Self-service password reset flow activity progress is typically caused by a password policy which requires users to rotate passwords. This operation already implies the user has signed in successfully and therefore the password reset is non-malicious.
let PerUserThreshold = 5;
let TotalThreshold = 100;
let action = dynamic(["change", "changed", "reset"]);
let pWord = dynamic(["password", "credentials"]);
let PasswordResetMultiDataSource =
(union isfuzzy=true
(//Password reset events
//4723: An attempt was made to change an account's password
//4724: An attempt was made to reset an accounts password
SecurityEvent
| where EventID in ("4723","4724")
| project TimeGenerated, Computer, AccountType, Account, Type, TargetUserName),
(//Password reset events
//4723: An attempt was made to change an account's password
//4724: An attempt was made to reset an accounts password
WindowsEvent
| where EventID in ("4723","4724")
| extend SubjectUserSid = tostring(EventData.SubjectUserSid)
| extend TargetUserName = tostring(EventData.TargetUserName)
| extend Account = strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend AccountType=case(Account endswith "$" or SubjectUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(SubjectUserSid), "", "User")
| project TimeGenerated, Computer, AccountType, Account, Type, TargetUserName),
(//Azure Active Directory Password reset events
AuditLogs
| where OperationName has_any (pWord) and OperationName has_any (action) and Result =~ "success"
| where OperationName !in (selfServicePasswordReset)
| mv-apply TargetResource = TargetResources on 
  (
      where TargetResource.type =~ "User"
      | extend AccountType = tostring(TargetResource.type),
               Account = tostring(InitiatedBy.user.userPrincipalName),
               TargetUserName = tolower(tostring(TargetResource.userPrincipalName))
  )
| project TimeGenerated, AccountType, Account, TargetUserName, Computer = "", Type),
(//OfficeActive ActiveDirectory Password reset events
OfficeActivity
| where OfficeWorkload == "AzureActiveDirectory"
| where (ExtendedProperties has_any (pWord) or ModifiedProperties has_any (pWord)) and (ExtendedProperties has_any (action) or ModifiedProperties has_any (action))
| extend AccountType = UserType, Account = OfficeObjectId
| project TimeGenerated, AccountType, Account, Type, Computer = ""),
(// Unix syslog password reset events
Syslog
| where Facility in ("auth","authpriv")
| where SyslogMessage has_any (pWord) and SyslogMessage has_any (action)
| extend AccountType = iif(SyslogMessage contains "root", "Root", "Non-Root")
| where SyslogMessage matches regex ".*password changed for.*"
| parse SyslogMessage with * "password changed for" Account
| project TimeGenerated, AccountType, Account, Computer = HostName, Type)
);
let pwrmd = PasswordResetMultiDataSource
| project TimeGenerated, Computer, AccountType, Account, Type, TargetUserName;
(union isfuzzy=true
(pwrmd
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), Computerlist = make_set(Computer, 25), AccountType = make_set(AccountType, 25), Computer = arg_max(Computer , TimeGenerated), TargetUserList = make_set(TargetUserName, 25), TargetUserName = arg_max(TargetUserName, TimeGenerated), Total=count() by Account, Type
| where Total > PerUserThreshold
| extend ResetPivot = "PerUserReset"),
(pwrmd
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), ComputerList = make_set(Computer, 25), AccountList = make_set(Account, 25), AccountType = make_set(AccountType, 25), Computer = arg_max(Computer , TimeGenerated), TargetUserList = make_set(TargetUserName, 25), TargetUserName = arg_max(TargetUserName, TimeGenerated), Total=count() by Type
| where Total > TotalThreshold
| extend ResetPivot = "TotalUserReset")
)
| extend timestamp = StartTimeUtc, HostName = tostring(split(Computer, '.', 0)[0]), DnsDomain = tostring(strcat_array(array_slice(split(Computer, '.'), 1, -1), '.')), Name = tostring(split(Account, '@', 0)[0]), UPNSuffix = tostring(split(Account, '@', 1)[0]), TargetName = tostring(split(TargetUserName,'@',0)[0]), TargetUPNSuffix = tostring(split(TargetUserName,'@',1)[0])

let endtime = 1d;
let starttime = 8d;
let threshold = 2.0;
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624 and LogonType == 10
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), ComputerCountToday = dcount(Computer), ComputerSet = makeset(Computer), ProcessSet = makeset(ProcessName)
by Account = tolower(Account), IpAddress, AccountType, Activity, LogonTypeName),
(WindowsEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624
| extend LogonType = tostring(EventData.LogonType)
| where  LogonType == 10
| extend ProcessName = tostring(EventData.ProcessName)
| extend Account = strcat(tostring(EventData.TargetDomainName),"\\", tostring(EventData.TargetUserName))
| extend IpAddress = tostring(EventData.IpAddress)
| extend TargetUserSid = tostring(EventData.TargetUserSid)
| extend AccountType=case(Account endswith "$" or TargetUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(TargetUserSid), "", "User")
| extend Activity="4624 - An account was successfully logged on."
| extend LogonTypeName="10 - RemoteInteractive"
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), ComputerCountToday = dcount(Computer), ComputerSet = makeset(Computer), ProcessSet = makeset(ProcessName)
by Account = tolower(Account), IpAddress, AccountType, Activity, LogonTypeName)
)
| join kind=inner (
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated >= ago(starttime) and TimeGenerated < ago(endtime)
| where EventID == 4624 and LogonType == 10
| summarize ComputerCountPrev7Days = dcount(Computer) by Account = tolower(Account), IpAddress
),
( WindowsEvent
| where TimeGenerated >= ago(starttime) and TimeGenerated < ago(endtime)
| where EventID == 4624  and EventData has ("10")
| extend LogonType = toint(EventData.LogonType)
| where  LogonType == 10
| extend Account = strcat(tostring(EventData.TargetDomainName),"\\", tostring(EventData.TargetUserName))
| extend IpAddress = tostring(EventData.IpAddress)
| summarize ComputerCountPrev7Days = dcount(Computer) by Account = tolower(Account), IpAddress)
)
) on Account, IpAddress
| extend Ratio = iff(isempty(ComputerCountPrev7Days), toreal(ComputerCountToday), ComputerCountToday / (ComputerCountPrev7Days * 1.0))
// Where the ratio of today to previous 7 days is more than double.
| where Ratio > threshold
| project StartTime, EndTime, Account, IpAddress, ComputerSet, ComputerCountToday, ComputerCountPrev7Days, Ratio, AccountType, Activity, LogonTypeName, ProcessSet
| extend AccountName = tostring(split(Account, @"\")[1]), AccountNTDomain = tostring(split(Account, @"\")[0])

let Keywords = dynamic(["helpdesk", " alert", " suspicious", "fake", "malicious", "phishing", "spam", "do not click", "do not open", "hijacked", "Fatal"]);
OfficeActivity
| where OfficeWorkload =~ "Exchange"
| where Parameters has "Deleted Items" or Parameters has "Junk Email"  or Parameters has "DeleteMessage"
| extend Events=todynamic(Parameters)
| parse Events  with * "SubjectContainsWords" SubjectContainsWords '}'*
| parse Events  with * "BodyContainsWords" BodyContainsWords '}'*
| parse Events  with * "SubjectOrBodyContainsWords" SubjectOrBodyContainsWords '}'*
| where SubjectContainsWords has_any (Keywords)
 or BodyContainsWords has_any (Keywords)
 or SubjectOrBodyContainsWords has_any (Keywords)
| extend ClientIPAddress = case( ClientIP has ".", tostring(split(ClientIP,":")[0]), ClientIP has "[", tostring(trim_start(@'[[]',tostring(split(ClientIP,"]")[0]))), ClientIP )
| extend Keyword = iff(isnotempty(SubjectContainsWords), SubjectContainsWords, (iff(isnotempty(BodyContainsWords),BodyContainsWords,SubjectOrBodyContainsWords )))
| extend RuleDetail = case(OfficeObjectId contains '/' , tostring(split(OfficeObjectId, '/')[-1]) , tostring(split(OfficeObjectId, '\\')[-1]))
| summarize count(), StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated) by  UserId, ClientIPAddress, ResultStatus, Keyword, OriginatingServer, OfficeObjectId, RuleDetail

OfficeActivity
| where OfficeWorkload =~ "Exchange"
| where Parameters has_any ("ForwardTo", "RedirectTo", "ForwardingSmtpAddress")
| mv-apply DynamicParameters = todynamic(Parameters) on (summarize ParsedParameters = make_bag(pack(tostring(DynamicParameters.Name), DynamicParameters.Value)))
| evaluate bag_unpack(ParsedParameters, columnsConflict='replace_source')
| extend DestinationMailAddress = tolower(case(
    isnotempty(column_ifexists("ForwardTo", "")), column_ifexists("ForwardTo", ""),
    isnotempty(column_ifexists("RedirectTo", "")), column_ifexists("RedirectTo", ""),
    isnotempty(column_ifexists("ForwardingSmtpAddress", "")), trim_start(@"smtp:", column_ifexists("ForwardingSmtpAddress", "")),
    ""))
| where isnotempty(DestinationMailAddress)
| mv-expand split(DestinationMailAddress, ";")
| extend ClientIPValues = extract_all(@'\[?(::ffff:)?(?P<IPAddress>(\d+\.\d+\.\d+\.\d+)|[^\]]+)\]?([-:](?P<Port>\d+))?', dynamic(["IPAddress", "Port"]), ClientIP)[0]
| extend ClientIP = tostring(ClientIPValues[0]), Port = tostring(ClientIPValues[1])
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), DistinctUserCount = dcount(UserId), UserId = make_set(UserId, 250), Ports = make_set(Port, 250), EventCount = count() by tostring(DestinationMailAddress), ClientIP
| where DistinctUserCount > 1
| mv-expand UserId to typeof(string)

let known_processes = (
  imProcess
  // Change these values if adjusting Query Frequency or Query Period
  | where TimeGenerated between(ago(14d)..ago(1d))
  | where Process has_any ("Policies\\{6AC1786C-016F-11D2-945F-00C04fB984F9}", "Policies\\{31B2F340-016D-11D2-945F-00C04FB984F9}")
  | summarize by Process);
  imProcess
  // Change these values if adjusting Query Frequency or Query Period
  | where TimeGenerated > ago(1d)
  | where Process has_any ("Policies\\{6AC1786C-016F-11D2-945F-00C04fB984F9}", "Policies\\{31B2F340-016D-11D2-945F-00C04FB984F9}")
  | where Process !in (known_processes)
  | summarize FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated) by Process, CommandLine, DvcHostname
  | extend HostName = tostring(split(DvcHostname, ".")[0]), DomainIndex = toint(indexof(DvcHostname, '.'))
  | extend HostNameDomain = iff(DomainIndex != -1, substring(DvcHostname, DomainIndex + 1), DvcHostname)
  | project-away DomainIndex

// Creating a list of successful sign-in by users in the last 7 days.
let KnownUserCountry = (
SigninLogs
| where TimeGenerated between (ago(7d) .. ago(1d) ) 
| where ResultType == 0
| summarize KnownCountry = make_set(Location,1048576) by UserPrincipalName
);
// Identify sign-ins that are no successful but have the auth details indicating a correct password.
SigninLogs
| where TimeGenerated >= ago(1d)
| where ResultType != 0
| extend ParseAuth = parse_json(AuthenticationDetails)
| extend AuthMethod = tostring(ParseAuth.[0].authenticationMethod),
    PasswordResult = tostring(ParseAuth.[0].authenticationStepResultDetail),
    AuthSucceeded = tostring(ParseAuth.[0].succeeded)
| where PasswordResult == "Correct Password" or AuthSucceeded == "true"
| where AuthMethod == "Password"
| extend failureReason = tostring(Status.failureReason)
| summarize NewCountry = make_set(Location,1048576), LastObservedTime = max(TimeGenerated), AppName = make_set(AppDisplayName,1048576) by UserPrincipalName, PasswordResult, AuthSucceeded, failureReason
// Combining both tables by user
| join kind=inner KnownUserCountry on UserPrincipalName
// Compare both arrays and identify if the country has been observed in the past.
| extend CountryDiff = set_difference(NewCountry,KnownCountry)
| extend CountryDiffCount = array_length(CountryDiff)
// Count the new column to only alert if there is a difference between both arrays
| where CountryDiffCount != 0
| extend NewCountryEvent = CountryDiff
// Getting UserName and Domain
| extend Name = split(UserPrincipalName,"@",0),
    Domain = split(UserPrincipalName,"@",1)
| mv-expand Name,Domain

(union isfuzzy=true
    (SecurityEvent
    | where EventID == 4720
    | where AccountType == "User"
    | project CreatedUserTime = TimeGenerated, CreatedUserEventID = EventID, CreatedUserActivity = Activity, Computer = toupper(Computer), 
    CreatedUser = tolower(TargetAccount), CreatedUserAccountName = TargetUserName, CreatedUserDomainName = TargetDomainName, CreatedUserSid = TargetSid, 
    AccountUsedToCreateUser = SubjectAccount, CreatedByAccountName = SubjectUserName, CreatedByDomainName = SubjectDomainName, SidofAccountUsedToCreateUser = SubjectUserSid
    ),
    (WindowsEvent
    | where EventID == 4720
    | extend SubjectUserSid = tostring(EventData.SubjectUserSid)
    | extend AccountType=case(EventData.SubjectUserName endswith "$" or SubjectUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(SubjectUserSid), "", "User")
    | where AccountType == "User"
    | extend SubjectAccount = strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
    | extend SubjectDomainName = tostring(EventData.SubjectDomainName), SubjectUserName = tostring(EventData.SubjectUserName)
    | extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
    | extend TargetUserName = tostring(EventData.TargetUserName), TargetDomainName = tostring(EventData.TargetDomainName) 
    | extend Activity="4720 - A user account was created."
    | extend TargetSid = tostring(EventData.TargetSid)
    | project CreatedUserTime = TimeGenerated, CreatedUserEventID = EventID, CreatedUserActivity = Activity, Computer = toupper(Computer), 
    CreatedUser = tolower(TargetAccount), CreatedUserAccountName = TargetUserName, CreatedUserDomainName = TargetDomainName, CreatedUserSid = TargetSid, 
    AccountUsedToCreateUser = SubjectAccount, CreatedByAccountName = SubjectUserName, CreatedByDomainName = SubjectDomainName, SidofAccountUsedToCreateUser = SubjectUserSid
    )
  )
| join kind=inner
(
  (union isfuzzy=true
    (SecurityEvent 
    | where AccountType == "User"
    // 4732 - A member was added to a security-enabled local group
    | where EventID == 4732
    // TargetSid is the builin Admins group: S-1-5-32-544
    | where TargetSid == "S-1-5-32-544"
    | project GroupAddTime = TimeGenerated, GroupAddEventID = EventID, GroupAddActivity = Activity, Computer = toupper(Computer), GroupName = tolower(TargetAccount), 
    GroupSid = TargetSid, AccountThatAddedUser = SubjectAccount, SIDofAccountThatAddedUser = SubjectUserSid, AddedByAccountName = SubjectUserName, AddedByDomainName = SubjectDomainName,
    CreatedUserSid = MemberSid
    ),
    (  WindowsEvent 
    // 4732 - A member was added to a security-enabled local group
    | where EventID == 4732 and EventData has "S-1-5-32-544"
    //TargetSid is the builin Admins group: S-1-5-32-544
    | extend SubjectUserSid = tostring(EventData.SubjectUserSid)
    | extend AccountType=case(EventData.SubjectUserName endswith "$" or SubjectUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(SubjectUserSid), "", "User")
    | where AccountType == "User"
    | extend TargetSid = tostring(EventData.TargetSid)
    | where TargetSid == "S-1-5-32-544"
    | extend SubjectAccount = strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
    | extend SubjectDomainName = tostring(EventData.SubjectDomainName), SubjectUserName = tostring(EventData.SubjectUserName)
    | extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
    | extend Activity="4732 - A member was added to a security-enabled local group."
    | extend MemberSid = tostring(EventData.MemberSid)
    | project GroupAddTime = TimeGenerated, GroupAddEventID = EventID, GroupAddActivity = Activity, Computer = toupper(Computer), GroupName = tolower(TargetAccount), 
    GroupSid = TargetSid, AccountThatAddedUser = SubjectAccount, SIDofAccountThatAddedUser = SubjectUserSid, AddedByAccountName = SubjectUserName, AddedByDomainName = SubjectDomainName,
    CreatedUserSid = MemberSid
    )
  )
)
on CreatedUserSid
//Create User first, then the add to the group.
| project Computer, CreatedUserTime, CreatedUserEventID, CreatedUserActivity, CreatedUser, CreatedUserSid, CreatedUserAccountName, CreatedUserDomainName,
GroupAddTime, GroupAddEventID, GroupAddActivity, GroupName, GroupSid,
AccountUsedToCreateUser, SidofAccountUsedToCreateUser, CreatedByAccountName, CreatedByDomainName, 
AccountThatAddedUser, SIDofAccountThatAddedUser, AddedByAccountName, AddedByDomainName
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex

//Query to find applications that have had application permissions granted to them

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName has "Add app role assignment to service principal"
| extend UpdatedPermission = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend AppName = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[4].newValue)))
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend AppId = tostring(TargetResources[1].id)
| project TimeGenerated, OperationName, UpdatedPermission, AppName, AppId, User

//Detect when either application or delegated access is granted to a service principal in Azure AD

//Data connector required for this query - Azure Active Directory - Audit Logs

let delegatedaccess=
    AuditLogs
    | where OperationName has "Add delegated permission grant"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend ['Service Principal ObjectId'] = tostring(TargetResources[1].id)
    | extend Activity = strcat("Delegated access added to application")
    | project
        TimeGenerated,
        Activity,
        ['Permissions granted'],
        ['Service Principal ObjectId'],
        Actor;
let appaccess=
    AuditLogs
    | where OperationName has "Add app role assignment to service principal"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Activity = strcat("Application access added to application")
    | extend ['Service Principal ObjectId'] = tostring(TargetResources[1].id)
    | project
        TimeGenerated,
        Activity,
        ['Permissions granted'],
        ['Service Principal ObjectId'],
        Actor;
union delegatedaccess, appaccess

//Query to find applications that have had delegated permissions granted to them

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where Category == "ApplicationManagement"
| where OperationName has "Add delegated permission grant"
| extend UpdatedPermissions = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue))) 
| extend AppId = tostring(TargetResources[1].id)
| project TimeGenerated, UpdatedPermissions, OperationName, AppId

//Detects users who have added a credential to an Azure AD App for the first time in 90 days, adjust timeframe as needed.

//Data connector required for this query - Azure Active Directory - Audit Logs

let timeframe = startofday(ago(90d));
AuditLogs
| where TimeGenerated > timeframe and TimeGenerated < startofday(now())
| where OperationName has "Update application – Certificates and secrets management"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project Actor
| join kind=rightanti
    (
    AuditLogs
    | where TimeGenerated > startofday(now())
    | where OperationName has "Update application – Certificates and secrets management"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend AppId = tostring(AdditionalDetails[1].value)
    | project TimeGenerated, Actor, AppId
    )
    on Actor
| project TimeGenerated, Actor, AppId

//Detect when a user adds delegated or application permissions to an Azure AD app for the first time.

//Data connector required for this query - Azure Active Directory - Audit Logs

//Look back in the last year to find all users who have added access to an app
let newusers=
AuditLogs
| where TimeGenerated > ago(365d) and TimeGenerated < ago(1d)
| where OperationName in ("Add app role assignment to service principal","Add delegated permission grant")
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| distinct Actor
//Find users who add access to an app for the first time in the last day
| join kind=rightanti (
AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName in ("Add app role assignment to service principal","Add delegated permission grant")
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| distinct Actor )
on Actor;
//Retrieve the list of permissions granted by the first time users
let delegatedaccess=
    AuditLogs
    | where TimeGenerated > ago(1d)
    | where OperationName has "Add delegated permission grant"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | where Actor in (newusers)
    | extend ['Service Principal ObjectId'] = tostring(TargetResources[1].id)
    | extend Activity = strcat("Delegated access added to application")
    | project
        TimeGenerated,
        Activity,
        ['Permissions granted'],
        ['Service Principal ObjectId'],
        Actor;
let appaccess=
    AuditLogs
    | where TimeGenerated > ago(1d)
    | where OperationName has "Add app role assignment to service principal"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | where Actor in (newusers)
    | extend Activity = strcat("Application access added to application")
    | extend ['Service Principal ObjectId'] = tostring(TargetResources[1].id)
    | project
        TimeGenerated,
        Activity,
        ['Permissions granted'],
        ['Service Principal ObjectId'],
        Actor;
union delegatedaccess, appaccess

//Find any Azure AD service principals that have been granted any .All access in the last year that haven't signed in for 30 days. May include already deleted service principals.

//Data connector required for this query - Azure Active Directory - Audit Logs

let delegatedaccess=
    AuditLogs
    | where TimeGenerated > ago(365d)
    | where OperationName has "Add delegated permission grant"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend ServicePrincipalId = tostring(TargetResources[1].id)
    | extend ['Permission type'] = strcat("Delegated")
    | summarize arg_max(TimeGenerated, *) by ServicePrincipalId
    | project
        TimeGenerated,
        ['Permission type'],
        ['Permissions granted'],
        ServicePrincipalId;
let appaccess=
    AuditLogs
    | where TimeGenerated > ago(365d)
    | where OperationName has "Add app role assignment to service principal"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend ['Permission type'] = strcat("Application")
    | extend ServicePrincipalId = tostring(TargetResources[1].id)
    | summarize arg_max(TimeGenerated, *) by ServicePrincipalId
    | project
        TimeGenerated,
        ServicePrincipalId,
        ['Permission type'],
        ['Permissions granted'];
union delegatedaccess, appaccess
| where ['Permissions granted'] contains ".All"
| distinct ServicePrincipalId
| join kind=leftanti (
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago (30d)
    | where ResultType == "0"
    | distinct ServicePrincipalName, ServicePrincipalId)
    on ServicePrincipalId

//Query to find OAuth applications where permissions were added and removed within 10 minutes

//Data connector required for this query - Azure Active Directory - Audit Logs

let PermissionAddedAlert=
    AuditLogs
    | where OperationName has "Add app role assignment to service principal"
    | extend UserWhoAdded = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend PermissionAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | extend AppId = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[5].newValue)))
    | extend TimeAdded = TimeGenerated
    | project UserWhoAdded, PermissionAdded, AppId, TimeAdded;
let PermissionRemovedAlert=
    AuditLogs
    | where OperationName has "Remove app role assignment from service principal"
    | extend UserWhoRemoved = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend PermissionRemoved = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].oldValue)))
    | extend AppId = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[5].newValue)))
    | extend TimeRemoved = TimeGenerated
    | project UserWhoRemoved, PermissionRemoved, AppId, TimeRemoved;
PermissionAddedAlert
| join kind=inner PermissionRemovedAlert on AppId
| where abs(datetime_diff('minute', TimeAdded, TimeRemoved)) <= 10
| extend TimeDiff = TimeAdded - TimeRemoved
| project
    TimeAdded,
    UserWhoAdded,
    PermissionAdded,
    AppId,
    TimeRemoved,
    UserWhoRemoved,
    PermissionRemoved,
    TimeDiff

//Summarize your Azure AD apps by what permissions they currently hold

//Data connector required for this query - Azure Active Directory - Audit Logs

//Find applications that have been deleted
let deletedapps=
AuditLogs
| where OperationName == "Remove service principal"
| extend ServicePrincipalId = tostring(TargetResources[0].id)
| project ServicePrincipalId;
let delegatedaccess=
    AuditLogs
    | where TimeGenerated > ago(365d)
    | where OperationName has "Add delegated permission grant"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend ServicePrincipalId = tostring(TargetResources[1].id)
    | extend ['Permission type'] = strcat("Delegated")
    | summarize arg_max(TimeGenerated, *) by ServicePrincipalId
//Exclude deleted applications
    | where ServicePrincipalId !in (deletedapps)
    | project
        TimeGenerated,
        ['Permission type'],
        ['Permissions granted'],
        ServicePrincipalId;
let appaccess=
    AuditLogs
    | where TimeGenerated > ago(365d)
    | where OperationName has "Add app role assignment to service principal"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend ['Permission type'] = strcat("Application")
    | extend ServicePrincipalId = tostring(TargetResources[1].id)
    | summarize arg_max(TimeGenerated, *) by ServicePrincipalId
//Exclude deleted applications
    | where ServicePrincipalId !in (deletedapps)
    | project
        TimeGenerated,
        ServicePrincipalId,
        ['Permission type'],
        ['Permissions granted'];
union delegatedaccess, appaccess
| mv-expand ['Permissions granted']
| where isnotempty( ['Permissions granted'])
//Extend new permission field
| summarize ['Permission List']=make_set(['Permissions granted']) by ['Permission type'], ServicePrincipalId
| extend ['Number of Permissions']=array_length(['Permission List']) 
| sort by ServicePrincipalId desc, ['Permission type'] asc

//Summarize the permissions granted to your Azure AD apps over the last year

//Data connector required for this query - Azure Active Directory - Audit Logs

//Find applications that have been deleted
let deletedapps=
AuditLogs
| where OperationName == "Remove service principal"
| extend ServicePrincipalId = tostring(TargetResources[0].id)
| project ServicePrincipalId;
let delegatedaccess=
    AuditLogs
    | where TimeGenerated > ago(365d)
    | where OperationName has "Add delegated permission grant"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend ServicePrincipalId = tostring(TargetResources[1].id)
    | extend ['Permission type'] = strcat("Delegated")
    | summarize arg_max(TimeGenerated, *) by ServicePrincipalId
//Exclude deleted applications
    | where ServicePrincipalId !in (deletedapps)
    | project
        TimeGenerated,
        ['Permission type'],
        ['Permissions granted'],
        ServicePrincipalId;
let appaccess=
    AuditLogs
    | where TimeGenerated > ago(365d)
    | where OperationName has "Add app role assignment to service principal"
    | extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | extend ['Permissions granted'] = split(x, ' ')
    | extend ['Permission type'] = strcat("Application")
    | extend ServicePrincipalId = tostring(TargetResources[1].id)
    | summarize arg_max(TimeGenerated, *) by ServicePrincipalId
//Exclude deleted applications
    | where ServicePrincipalId !in (deletedapps)
    | project
        TimeGenerated,
        ServicePrincipalId,
        ['Permission type'],
        ['Permissions granted'];
union delegatedaccess, appaccess
| mv-expand ['Permissions granted']
| where isnotempty( ['Permissions granted'])
//Extend new permission field
| extend Permission = strcat(['Permission type']," - ",['Permissions granted'])
| summarize PermissionCount=count()by Permission
| sort by PermissionCount desc

//Summarize your Azure AD service principals by the last time they signed in, grouped by month

//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs

AADServicePrincipalSignInLogs
| project TimeGenerated, AppId, ResultType, ServicePrincipalName
| where TimeGenerated > ago (360d)
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by AppId
| summarize ['Application List']=make_set(ServicePrincipalName) by Month=startofmonth(TimeGenerated)
| sort by Month asc

//Tracks privileged post creation events on your Azure AD service principals, such as secrets being generated, permissions being added or an admin consenting.

//Data connector required for this query - Azure Active Directory - Audit Logs

//Events are then summarized by operation and time. Add a specific Application ID to track events for that one app.
let timeframe=60d;
let AppAdded=
    AuditLogs
    | where TimeGenerated > ago (timeframe)
    | where OperationName == "Add service principal"
    | extend AppId = tostring(AdditionalDetails[1].value)
    | extend ServicePrincipalId = tostring(TargetResources[0].id)
    // Optionally add a specific Application ID
    //| where AppId == "id"
    | extend AppName = tostring(TargetResources[0].displayName)
    | where isnotempty(AppId)
    | project TimeGenerated, OperationName, AppId, AppName, ServicePrincipalId;
let AppSecretAdded=
    AuditLogs
    | where OperationName contains 'Update application – Certificates and secrets management'
    | extend AppId = tostring(AdditionalDetails[1].value)
    | project TimeGenerated, AppId, OperationName
    | join kind=inner AppAdded on AppId
    | project TimeGenerated, OperationName, AppId, AppName;
let AppApplicationAccess=
    AuditLogs
    | where OperationName == "Add app role assignment to service principal"
    | extend AppId = tostring(TargetResources[1].displayName)
    | project TimeGenerated, AppId, OperationName
    | join kind=inner AppAdded on AppId
    | project TimeGenerated, OperationName, AppId, AppName;
let AppDelegatedAccess=
    AuditLogs
    | where OperationName == "Add delegated permission grant"
    | extend ServicePrincipalId = tostring(TargetResources[1].id)
    | project TimeGenerated, ServicePrincipalId, OperationName
    | join kind=inner AppAdded on ServicePrincipalId
    | project TimeGenerated, OperationName, AppId, AppName;
let AppConsentGiven=
    AuditLogs
    | where OperationName == "Consent to application"
    | extend AppId = tostring(AdditionalDetails[1].value)
    | project TimeGenerated, AppId, OperationName
    | join kind=inner AppAdded on AppId
    | project TimeGenerated, OperationName, AppId, AppName;
let AppDeleted=
    AuditLogs
    | where OperationName == "Delete application"
    | extend AppId = tostring(AdditionalDetails[1].value)
    | project TimeGenerated, AppId, OperationName
    | join kind=inner AppAdded on AppId
    | project TimeGenerated, OperationName, AppId, AppName;
AppAdded
| union
    AppSecretAdded,
    AppApplicationAccess,
    AppConsentGiven,
    AppDelegatedAccess,
    AppDeleted
| sort by TimeGenerated asc 
| summarize
    AppOperations=(make_list(OperationName)),
    AppOperationTime=(make_list(TimeGenerated))
    by AppId, AppName

let OMIVulnerabilityPatchVersion = "OMIVulnerabilityPatchVersion:1.13.40-0";
Heartbeat
| where Category == "Direct Agent"
| summarize arg_max(TimeGenerated,*) by Computer
| parse strcat("Version:" , Version) with * "Version:" Major:long "."
Minor:long "." Patch:long "-" *
| parse OMIVulnerabilityPatchVersion with * "OMIVulnerabilityPatchVersion:"
OMIVersionMajor:long "." OMIVersionMinor:long "." OMIVersionPatch:long "-" *
| where Major <OMIVersionMajor or (Major==OMIVersionMajor and Minor
<OMIVersionMinor) or (Major==OMIVersionMajor and Minor==OMIVersionMinor and
Patch<OMIVersionPatch) 
| project Version, Major,Minor,Patch,
Computer,ComputerIP,OSType,OSName,ResourceId

// Finds guest accounts who were added to a Team and then downloaded documents straight away. 

//Data connector required for this query - Office 365

// Startime = Amount of time to look back on, i.e last 7 days.
// Timeframe = looks for downloads for this period after being added to the Team, i.e 2 hours after being added.
let starttime = 7d;
let timeframe = 2h;
let operations = dynamic(["FileSyncDownloadedFull", "FileDownloaded"]);
OfficeActivity
| where TimeGenerated > ago(starttime)
| where Operation == "MemberAdded"
| mv-expand Members
| extend UserAdded = tostring(Members.UPN)
| where UserAdded contains "#EXT#"
| where CommunicationType == "Team"
| project TimeAdded=TimeGenerated, UserId=tolower(UserAdded)
| join kind=inner
    (
    OfficeActivity
    | where TimeGenerated > ago(starttime)
    | where Operation in (['operations'])
    )
    on UserId
| project DownloadTime=TimeGenerated, TimeAdded, FileName=SourceFileName, UserId
| where (DownloadTime - TimeAdded) between (0min .. timeframe)
//Optionally summarize the data into the activity by each guest
| summarize
    ['Count of Files Downloaded']=count(),
    ['List of Files Downloaded']=make_set(FileName)
    by UserId
| sort by ['Count of Files Downloaded'] desc

//Detect anomalies in the amount of downloads from your Office 365 tenant by guest accounts.

//Data connector required for this query - Office 365

//Starttime and endtime = which period of data to look at, i.e from 21 days ago until today.
let startdate=21d;
let enddate=1d;
//Timeframe = time period to break the data up into, i.e 1 hour blocks.
let timeframe=1h;
//Sensitivity = the lower the number the more sensitive the anomaly detection is, i.e it will find more anomalies, default is 1.5
let sensitivity=2;
//Threshold = set this to tune out low count anomalies, i.e when total downloads are only over 300 per hour
let threshold=300;
let outlierusers=
OfficeActivity
| where TimeGenerated between (startofday(ago(startdate))..startofday(ago(enddate)))
| where Operation in ("FileSyncDownloadedFull","FileDownloaded")
| where UserId contains "#EXT#"
| make-series GuestDownloads=count() on TimeGenerated from startofday(ago(startdate)) to startofday(ago(enddate)) step timeframe by UserId
| extend outliers=series_decompose_anomalies(GuestDownloads, sensitivity)
| mv-expand TimeGenerated, GuestDownloads, outliers
| where outliers == 1 and GuestDownloads > threshold
//Optionally visualize the anomalies - remove everything below this line to just retrieve the data instead of visualizing
| distinct UserId;
OfficeActivity
| where TimeGenerated between (startofday(ago(startdate))..startofday(ago(enddate)))
| where Operation in ("FileSyncDownloadedFull","FileDownloaded")
| where UserId contains "#EXT#"
| where UserId in (outlierusers)
| make-series GuestDownloads=count() default=0 on TimeGenerated from startofday(ago(startdate)) to startofday(ago(enddate)) step timeframe by UserId 
| render timechart with (ytitle="Download Count",title="Anomalous Guest Downloads from Office 365")

//Detect anomalies in the amount of files shared to guests in your Office 365 tenant from your users.

//Data connector required for this query - Office 365

//Starttime and endtime = which period of data to look at, i.e from 21 days ago until today.
let startdate=21d;
let enddate=1d;
//Timeframe = time period to break the data up into, i.e 1 hour blocks.
let timeframe=1h;
//Sensitivity = the lower the number the more sensitive the anomaly detection is, i.e it will find more anomalies, default is 1.5
let sensitivity=2;
//Threshold = set a threshold to account for low volume anomailies, i.e moving from 1 file shared to 2 within an hour
let threshold = 5;
let outlierusers=
OfficeActivity
| where Operation in ("AddedToSecureLink","SecureLinkCreated","SecureLinkUpdated")
| where TargetUserOrGroupType == "Guest" or TargetUserOrGroupName contains "#ext#"
| make-series GuestFileShares=count() on TimeGenerated from startofday(ago(startdate)) to startofday(ago(enddate)) step timeframe by UserId
| extend outliers=series_decompose_anomalies(GuestFileShares, sensitivity)
| mv-expand TimeGenerated, GuestFileShares, outliers
| where outliers == 1 and GuestFileShares > threshold
//Optionally visualize the anomalies - remove everything below this line to just retrieve the data instead of visualizing
| distinct UserId;
OfficeActivity
| where Operation in ("AddedToSecureLink","SecureLinkCreated","SecureLinkUpdated")
| where TargetUserOrGroupType == "Guest" or TargetUserOrGroupName contains "#ext#"
| where UserId in (outlierusers)
| make-series GuestFileShares=count() default=0 on TimeGenerated from startofday(ago(startdate)) to startofday(ago(enddate)) step timeframe by UserId 
| render timechart with (ytitle="Share Count",title="Anomalous Files Shared to Guests")

//Find the top 20 users who are downloading files from your tenant from untrusted devices and calculate the percentage of downloads from those users vs all untrusted downloads. Useful to see if you have a few users responsible for most of the downloads in your tenant.

//Data connector required for this query - Office 365
//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where UserType == "Member"
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| distinct UserPrincipalName, IPAddress, DeviceTrustType
| join kind=inner(
    OfficeActivity
    | where TimeGenerated > ago(30d)
    | where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
    )
    on $left.UserPrincipalName == $right.UserId, $left.IPAddress == $right.ClientIP
| where isempty(DeviceTrustType)
| summarize Count=count() by UserPrincipalName
| as T
| extend Percentage = round(100.0 * Count / toscalar (T
    | summarize sum(Count)), 2)
| project-reorder UserPrincipalName, Count, Percentage
| top 20 by Percentage desc

//Find the top 20 guests who are downloading files from your tenant and calculate the percentage of total downloads from those users. Useful to see if you have a few guests responsible for most of the downloads in your tenant.

//Data connector required for this query - Office 365

//First find the count of all downloads by guests in your tenant
let totalguestdownloads=
    OfficeActivity
    | where TimeGenerated > ago(30d)
    | where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
    | where UserId contains "#EXT#"
    | count
//Extend a fake column we will use to join our two queries
    | extend ['Total Download Count'] = Count, Constant="x";
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
//Extend the same fake column to use to join our two queries
| extend Constant="x"
| extend ['Guest UserPrincipalName'] = tostring(split(UserId, "#")[0])
//Summarize download count by each guest and join to our first query
| summarize ['Individual Download Count']=count()by ['Guest UserPrincipalName'], Constant
| join kind=fullouter totalguestdownloads on Constant
| project-away Constant, Constant1, Count
| sort by ['Individual Download Count'] desc 
//Take the top 20 and then calculate the percentage
| take 20
| extend ['Percentage of Total Downloads']=(todouble(['Individual Download Count']) * 100 / todouble(['Total Download Count']))

//Calculate the percentage that each guest domain is contributing to total downloads from your Office 365 tenant

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
| extend ['Guest UserPrincipalName'] = tostring(split(UserId, "#")[0])
| extend ['Guest Domain'] = tostring(split(['Guest UserPrincipalName'], "_")[-1])
| summarize Count=count() by ['Guest Domain']
| as T
| extend Percentage = round(100.0 * Count / toscalar (T
    | summarize sum(Count)), 2)
| project-reorder ['Guest Domain'], Count, Percentage
| sort by Percentage desc

//Calculate the time Office 365 took to detect malware after the file was uploaded

//Data connector required for this query - Office 365

//First find the malware detection event
OfficeActivity
| where TimeGenerated > ago(60d)
| where Operation == "FileMalwareDetected"
| project
    DetectionTime=TimeGenerated,
    OfficeWorkload,
    ['File Name']=SourceFileName,
    ['File Location']=OfficeObjectId
//Then join back to the upload event on the same file location
| join kind=inner 
    (
    OfficeActivity
    | where TimeGenerated > ago (60d)
    | where Operation in ("FileUploaded", "FileSyncUploadedFull")
    | project
        UploadTime=TimeGenerated,
        OfficeWorkload,
        ['File Name']=SourceFileName,
        ['File Location']=OfficeObjectId,
        ['Relative File URL']=SourceRelativeUrl
    | summarize min(UploadTime) by ['File Location'], UploadTime
    )
    on ['File Location']
//Calculate the time difference between upload and malware detection
| project
    ['File Name'],
    UploadTime,
    DetectionTime,
    ['Time Difference in Minutes']=datetime_diff("minute", DetectionTime, UploadTime),
    ['File Location']

//Detects users with global or exchange administrator roles who have accessed email items from mailboxes other than their own

//Data connector required for this query - Office 365
//Data connector required for this query - Microsoft Sentinel UEBA

let timeframe=30d;
let adminusers=
IdentityInfo
| where TimeGenerated > ago(21d)
| where AssignedRoles has_any ("Exchange Administrator", "Global Administrator")
| summarize arg_max(TimeGenerated, *) by AccountUPN
| project UserId=AccountUPN;
OfficeActivity
| where TimeGenerated > ago(timeframe)
| where OfficeWorkload == "Exchange"
| where Operation == "MailItemsAccessed"
| where UserId in (adminusers)
| where UserId != MailboxOwnerUPN
| project AccessTime=TimeGenerated, UserId, MailboxOwnerUPN, Folders

//Detect when an Exchange admin grants full mailbox access to another user

//Data connector required for this query - Office 365

OfficeActivity
| where RecordType == "ExchangeAdmin"
| where Operation == "Add-MailboxPermission"
| parse-where Parameters with * 'Identity","Value":"' TargetMailbox '"' *
| parse-where Parameters with * 'User","Value":"' UserGivenAccess '"' *
| parse-where Parameters with * 'AccessRights","Value":"' AccessRights '"' *
| project
    TimeGenerated,
    Actor=UserId,
    ['Target Mailbox']=TargetMailbox,
    ['Target Mailbox DisplayName']=OfficeObjectId,
    ['User Granted Access']=UserGivenAccess,
    ['Access Type']=AccessRights
| where tolower(Actor) != "nt authority\\system (microsoft.exchange.servicehost)"
| sort by TimeGenerated desc

//Detect when a new Exchange admin role is created and parse the permissions

//Data connector required for this query - Office 365

OfficeActivity
| where Operation == "New-RoleGroup"
| where RecordType == "ExchangeAdmin"
| parse Parameters with * 'Name","Value":"' ['Role Name'] '"' *
| parse Parameters with * 'Roles","Value":"' ['Permissions Added'] '"' *
| project TimeGenerated, Actor=UserId, ['Role Name'], ['Permissions Added']

//Detect when a user is made an owner on multiple Teams in a short time frame.

//Data connector required for this query - Office 365

//Define a time period to check and the threshold of how many Teams to alert on.
//This example would find users added as an owner to 3 or more Teams within 30 minutes.
let timeframe=30m;
let threshold=3;
OfficeActivity
| where TimeGenerated > ago(1d)
| where Operation == "MemberRoleChanged"
| mv-expand Members
| extend RoleAdded = tostring(Members.Role)
| extend UserAdded = tostring(Members.UPN)
| where RoleAdded == 2
| project TimeGenerated, RoleAdded, UserAdded, TeamName
| summarize
    ['Number of Teams Made Owner']=dcount(TeamName), ['Team Names']=make_set(TeamName) by UserAdded, bin(TimeGenerated, timeframe)
| where ['Number of Teams Made Owner'] >= threshold

//Detect when a new scoping policy is applied, scoping policies are used to limit permissions to Exchange mailboxes being accessed via OAuth. They should be configured with least privilege

//Data connector required for this query - Office 365

OfficeActivity
| where Operation == "New-ApplicationAccessPolicy"
| extend GroupPolicyAppliedTo = tostring(parse_json(Parameters)[1].Value)
| extend AppId = tostring(parse_json(Parameters)[0].Value)
| extend AccessRight = tostring(parse_json(Parameters)[2].Value)
| project TimeGenerated, Actor=UserId, Operation, AccessRight, GroupPolicyAppliedTo, AppId

//Find when files are shared from OneDrive to third party guests

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(7d)
| where OfficeWorkload == "OneDrive"
| where Operation in ("SecureLinkCreated", "AddedToSecureLink")
| where TargetUserOrGroupType == "Guest" or TargetUserOrGroupName contains "#EXT#"
| project
    TimeGenerated,
    ['User Who Shared']=UserId,
    ['Guest Granted Access']=TargetUserOrGroupName,
    ['File Shared']=OfficeObjectId
| sort by TimeGenerated desc

//Find any new operations audited in Office 365 in the last 14 days vs the previous 180 days

//Data connector required for this query - Office 365

let existingoperations=
    OfficeActivity
    | where TimeGenerated > ago(180d) and TimeGenerated < ago(14d)
    | distinct Operation;
OfficeActivity
| where TimeGenerated > ago(14d)
| summarize arg_min(TimeGenerated, *) by Operation
| where Operation !in (existingoperations)
| project ['Time First Seen']=TimeGenerated, Operation, OfficeWorkload

//When Office 365 detects malware in OneDrive or SharePoint find any users that downloaded the same file

//Data connector required for this query - Office 365

let malware=
    OfficeActivity
    | where TimeGenerated > ago(1d)
    | where Operation == "FileMalwareDetected"
    | distinct OfficeObjectId;
OfficeActivity
| where TimeGenerated > ago (1d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where OfficeObjectId in (malware)
| summarize ['Users who Downloaded']=make_set(UserId) by ['File Name']=OfficeObjectId

//Detect when a guest is added to multiple Teams in a short time frame.

//Data connector required for this query - Office 365

//Define a time period to check and the threshold of how many Teams to alert on.
let timeframe=15m;
let threshold=2;
OfficeActivity
| where TimeGenerated > ago(1d)
| where Operation == "MemberAdded"
| mv-expand Members
| extend UserAdded = tostring(Members.UPN)
| where UserAdded contains "#EXT#"
| where CommunicationType == "Team"
| summarize
    ['Number of Teams Guest Added To']=dcount(TeamName), ['Team Names']=make_set(TeamName) by UserAdded, bin(TimeGenerated, timeframe)
| where ['Number of Teams Guest Added To'] >= threshold

//Summarize the total count of downloads from Office 365 for each of your guest domains

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
| extend ['Guest UserPrincipalName'] = tostring(split(UserId,"#")[0])
| extend ['Guest Domain'] = tostring(split(['Guest UserPrincipalName'],"_")[-1])
| project ['Guest Domain']
| summarize ['Download Count']=count()by ['Guest Domain']
| sort by ['Download Count'] desc

//Query to retrieve the name of inbox rules created via mv-apply

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago (30d)
| where Operation == "New-InboxRule"
| mv-apply p=todynamic(Parameters) on (
where p.Name == "Name"
| extend RuleName = tostring(p.Value)
)
| project TimeGenerated, UserId, ClientIP, RuleName
//Additionally search for inbox rule names that have no alphanumeric characters, can be a sign of threat actor activity
| where RuleName matches regex @"^[^a-zA-Z0-9]*$"

//Alerts when a file believed to be malware is uploaded to your Office 365 tenant in SharePoint or OneDrive

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation == "FileMalwareDetected"
| project
    TimeGenerated,
    OfficeWorkload,
    ['File Name']=SourceFileName,
    ['File Location']=OfficeObjectId,
    ['Relative File URL']=SourceRelativeUrl,
    ClientIP

//Detect when a user shares multiple files to Azure AD guests over a short time frame.

//Data connector required for this query - Office 365

//Define a time period to check and the threshold of how many files to alert on.
//In this example it would detect when a user shares 10 or more files to a guest within 30 minutes
let timeframe=30m;
let threshold=10;
OfficeActivity
| where TimeGenerated > ago(1d)
| where Operation in ("SecureLinkCreated", "AddedToSecureLink")
| where TargetUserOrGroupType == "Guest" or TargetUserOrGroupName contains "#EXT#"
| summarize
    ['File Share Count']=dcount(OfficeObjectId),
    ['List of Files']=make_set(OfficeObjectId)
    by UserId, bin(TimeGenerated, timeframe)
| where ['File Share Count'] >= threshold

//Detect when an app is installed into Teams for the first time compared to the previous timerange

//Data connector required for this query - Office 365

let knownapps=
    OfficeActivity
    | where TimeGenerated > ago(180d) and TimeGenerated < ago(7d)
    | where OfficeWorkload == "MicrosoftTeams"
    | where Operation == "AppInstalled"
    | distinct AzureADAppId;
OfficeActivity
| where TimeGenerated > ago (7d)
| where OfficeWorkload == "MicrosoftTeams"
| where Operation == "AppInstalled"
| where AzureADAppId !in (knownapps)
| project TimeGenerated, UserId, AddonName, AzureADAppId

//Detect when a shared Teams channel is created

//Data connector required for this query - Office 365

OfficeActivity
| where Operation == "ChannelAdded"
| where ChannelType == "Shared"
| project TimeGenerated, Actor=UserId, TeamName, ChannelType, ChannelName

//Summarize the total count and the list of files downloaded by guests in your Office 365 tenant

//Data connector required for this query - Office 365

let timeframe=7d;
OfficeActivity
| where TimeGenerated > ago(timeframe)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
| summarize
    ['Count of Downloads']=count(),
    ['List of Files Downloaded']=make_set(OfficeObjectId)
    by UserId
| sort by ['Count of Downloads'] desc

//Find any of your Teams that have had guests added to them in the last week and arrange by the Teams with the most guests added.

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(7d)
| where Operation == "MemberAdded"
| mv-expand Members
| extend UserAdded = tostring(Members.UPN)
| where UserAdded contains "#EXT#"
| where CommunicationType == "Team"
| summarize
    ['Number of Guests Added']=dcount(UserAdded),
    ['List of Guests Added']=make_set(UserAdded)
    by TeamName
| sort by ['Number of Guests Added'] desc

//Summarize the applications installed into Teams in the last month. Apps are grouped into the scope they were installed to - Team, Chat or User and by name and application id

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago (30d)
| where Operation == "AppInstalled"
| summarize
    ['App Installed to Team Scope']=countif(OperationScope == "Team"),
    ['App Installed to Chat Scope']=countif(OperationScope == "Chat"),
    ['App Installed to User Scope']=countif(OperationScope == "User")
    by AddonName, AzureADAppId
| sort by AddonName asc

//Create a weekly summary of Teams created and deleted in your Office 365 tenant

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("TeamCreated", "TeamDeleted")
| summarize
    ['Count of Teams Created']=dcountif(TeamName, Operation == "TeamCreated"),
    ['List of Teams Created']=make_set_if(TeamName, Operation == "TeamCreated"),
    ['Count of Teams Deleted']=dcountif(TeamName, Operation == "TeamDeleted"),
    ['List of Teams Deleted']=make_set_if(TeamName, Operation == "TeamDeleted")
    by Week=startofweek(TimeGenerated)
| sort by Week desc

//Create a set of users external to your organization who have accessed Office files after being shared. Events are grouped by the user who shared the document, and what activities were performed against it and by which external account.

//Data connector required for this query - Office 365

OfficeActivity
| project LinkCreatedTime=TimeGenerated, Operation, UserWhoShared=UserId, OfficeObjectId
| where Operation in ('AddedToSecureLink', 'SecureLinkCreated', 'SecureLinkUpdated', 'SharingInvitationCreated')
| join kind=inner
    (OfficeActivity
    | project LinkClickedTime=TimeGenerated, Operation, UserWhoAccessed=UserId, OfficeObjectId)
    on OfficeObjectId
| where UserWhoAccessed !endswith "yourdomain.com" and UserWhoAccessed != "app@sharepoint"
| extend ExternalOperation=Operation1
| summarize ExternalUsers=make_set(UserWhoAccessed) by UserWhoShared, OfficeObjectId, ExternalOperation

//Detect when the role for a user changes to owner or back to standard member in your any of your Teams

//Data connector required for this query - Office 365

OfficeActivity
| where Operation == "MemberRoleChanged"
| mv-expand Members
| extend User = tostring(Members.UPN)
| extend x = tostring(Members.Role)
| extend Action = case(x == "1", strcat("User changed to member"),
    x == "2", strcat("User changed to owner"), "unknown")
| where Action in ("User changed to member", "User changed to owner")
| project
    TimeGenerated,
    TeamName,
    ActorType=UserType,
    Actor=UserId,
    UserAdded=User,
    Action

//Find the top 20 of a collection of varied data sets, no real detections in here just interesting data that is captured

//Data connector required for this query - Office 365

//Top 20 files downloaded from your tenant
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| summarize Count=count()by OfficeObjectId
| top 20 by Count

//Top 20 users downloading files from your tenant
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId !contains "#EXT#"
| summarize Count=count()by UserId
| top 20 by Count

//Top 20 guests downloading files from your tenant
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
| extend ['Guest UserPrincipalName'] = tostring(split(UserId,"#")[0])
| summarize Count=count()by ['Guest UserPrincipalName']
| top 20 by Count

//Top 20 downloads from your tenant by guest domain
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
| extend ['Guest UserPrincipalName'] = tostring(split(UserId,"#")[0])
| extend ['Guest Domain'] = tostring(split(['Guest UserPrincipalName'],"_")[-1])
| summarize Count=count()by ['Guest Domain']
| top 20 by Count

//Top 20 users sharing files to guests
OfficeActivity
| where TimeGenerated > ago(30d)
| where OfficeWorkload == "OneDrive"
| where Operation in ("SecureLinkCreated", "AddedToSecureLink")
| where TargetUserOrGroupType == "Guest" or TargetUserOrGroupName contains "#EXT#"
| summarize Count=count() by UserId
| top 20 by Count

//Top 20 most shared files
OfficeActivity
| where TimeGenerated > ago(30d)
| where OfficeWorkload == "OneDrive"
| where Operation in ("SecureLinkCreated", "AddedToSecureLink")
| summarize Count=count() by OfficeObjectId
| top 20 by Count

//Top 20 guests with files shared to them
OfficeActivity
| where TimeGenerated > ago(30d)
| where OfficeWorkload == "OneDrive"
| where Operation in ("SecureLinkCreated", "AddedToSecureLink")
| where TargetUserOrGroupType == "Guest" or TargetUserOrGroupName contains "#EXT#"
| summarize Count=count() by TargetUserOrGroupName
| top 20 by Count

//Top 20 guests added to Teams by distinct Team name
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation == "MemberAdded"
| mv-expand Members
| extend UserAdded = tostring(Members.UPN)
| where UserAdded contains "#EXT#"
| where CommunicationType == "Team"
| where UserId != "Microsoft Teams Sync"
| summarize Count=dcount(TeamName) by UserId
| top 20 by Count

//Visualises potentially anomalous download activities in your Office tenant over the set period. Time frames can be adjusted to suit. 

//Data connector required for this query - Office 365

//Starttime and timeframe = how many days of data to look at and in what grouping, i.e 7 days of data over 1 hour periods.
//Threshold = the amount of total downloads required to be included in anomaly calculations. Reduces noise from low level anomalies, e.g going from 1 download to 3 downloads total.
//sensitivity = adjust to make the query more or less sensitive, the higher the value, the greater the anomaly required to be detected.
let starttime = 7d;
let timeframe = 1h;
let threshold = 30;
let sensitivity = 2;
let operations = dynamic(["FileSyncDownloadedFull","FileDownloaded"]);
let outlierusers=
OfficeActivity
| where TimeGenerated > ago(starttime)
| where Operation in (['operations'])
| project TimeGenerated, UserId
| order by TimeGenerated
| summarize Events=count()by UserId, bin(TimeGenerated, timeframe)
| where Events > threshold
| summarize EventCount=make_list(Events),TimeGenerated=make_list(TimeGenerated) by UserId
| extend outliers=series_decompose_anomalies(EventCount,sensitivity)
| mv-expand TimeGenerated, EventCount, outliers
| where outliers == 1
| distinct UserId;
OfficeActivity
| where TimeGenerated > ago(starttime)
| where Operation in (['operations'])
| where UserId in (outlierusers)
| summarize DownloadCount=count()by UserId, bin(TimeGenerated, timeframe)
| render timechart