IdentityInfo

| where AccountUPN == Nledec@company.com

//| project AccountDisplayName, AccountDomain, AccountObjectId, AccountTenantId, AdditionalMailAddresses, Applications, AssignedRoles, BlastRadius, LastSeenDate, Manager, RelatedAccounts, RiskLevelDetails, TimeGenerated, Type, UserType, UserState

 

///you can rung just the table at the beginning to see what fields you get, then you can filter them out.

 

BehaviorAnalytics

| project UserName, UserPrincipalName, ActionType, ActivityInsights, DestinationDevice, DestinationIPAddress, DestinationIPLocation, DevicesInsights, EventSource, InvestigationPriority, SourceDevice, SourceIPAddress, TenantId, TimeGenerated, TimeProcessed, Type

| where UserPrincipalName == Nledec@company.com

//| where SourceIPAddress == "45.58.45.216"

 

 

*************************************************************************

 

This is most probably for Microsoft Sentinel:

 

search *

| where TimeGenerated > ago(5d)

| where * contains "sign"

| summarize count() by $table

 

search *

| where TimeGenerated > ago(5d)

| where * contains "user"

| summarize count() by $table

 

//get the schema for any table, sample below got it after running the above.

 

EmailPostDeliveryEvents

| getschema

 

Anomalies

| where TimeGenerated > ago(5d)

| sample 100

 

****************** or

 

search *

| where TimeGenerated > ago(5d)

| where * contains "syslog"

| summarize count() by $table

 

EmailUrlInfo

| getschema

 

EmailUrlInfo

| where TimeGenerated > ago(5d)

| sample 100

 

search *

| where TimeGenerated > ago(5d)

| where * contains "sign"

| summarize count() by $table

 

search *

| where TimeGenerated > ago(5d)

| where * contains "Anomalies"

| summarize count() by $table

 

Anomalies

| getschema

 

AADNonInteractiveUserSignInLogs

| where TimeGenerated > ago(5d)

| sample 100

 

AzureActivity

| summarize count() by CallerIpAddress, Caller, ActivityStatus, HTTPRequest, Authorization

 

Anomalies

| getschema

 

SigninLogs

| getschema

 

Anomalies

| project Description, AnomalyReasons, DestinationDevice, DestinationIpAddress, DestinationLocation, Id, Type, UserName

 

****************************************************************************

// the following kql is going to bring a list of all the applications that each user has accessed., people that should not

//be clicking around.

 

SigninLogs
| summarize Applications=make_set(AppDisplayName) by UserPrincipalName

 

//****************************

//the following query is going to tell us which user is connecting to the most unique applications

 

SigninLogs
| summarize Applications=make_set(AppDisplayName) by UserPrincipalName
| extend AppCount = array_length(Applications)

 

SigninLogs

| summarize Applications=make_set(AppDisplayName) by UserPrincipalName

| extend AppCount = array_length(Applications)

| where UserPrincipalName == Nledec@company.com

 

**********************************************************************

//each app has a service principal sitting in AAD potentially with some privileges as well, it's good practice to get alerted and delete those apps if they are not used.

 

// Single Sign On Applications

SigninLogs

| where TimeGenerated > ago(90d)

| summarize arg_max(TimeGenerated, *) by AppId

| project AppDisplayName, ['Last Logon Time']=TimeGenerated, ['Days Since Last Logon']=datetime_diff("day",now(),TimeGenerated), AADTenantId, AlternateSignInName

| where ['Days Since Last Logon'] > 30

//| project AppDisplayName

 

**********************************************************************

Unfamiliar sign in properties - find and can replace by another alert

 

let site_IP = _GetWatchlist('IPetEmailu');

SecurityAlert

//| extend IP_entity = tostring(parse_json(Entities).[0]['Address']

| where DisplayName  == "Unfamiliar sign-in properties"

//| join site_IP on $left.IP_entity ==$right.IP

| project TimeGenerated, DisplayName, AlertSeverity, ProviderName, Entities, ProductName, Type, Status, CompromisedEntity, Description

 

///////////////or

 

let site_IP = _GetWatchlist('IPetEmailu');

SecurityAlert

//| extend IP_entity = tostring(parse_json(Entities).[0]['Address']

| where DisplayName  == "Administrative action submitted by an Administrator"

//| join site_IP on $left.IP_entity ==$right.IP

| project TimeGenerated, DisplayName, AlertSeverity, ProviderName, Entities, ProductName, Type, Status, CompromisedEntity, Description

 

////////////////////////////////////////////////////////////////

////exception list, IPs not in my list

let site_IP = _GetWatchlist('IPetEmailu');

let timeFrame = 1d;

let logonDiff = 10m;

let allowlist = (_GetWatchlist('IPetEmailu') | project 'IPAddress');

SigninLogs

| where TimeGenerated >= ago(timeFrame)

| where IPAddress !in (allowlist)

| project TimeGenerated, AlternateSignInName, AppDisplayName, AuthenticationMethodsUsed, AutonomousSystemNumber, Category, ClientAppUsed, DeviceDetail, HomeTenantId, Location, LocationDetails, NetworkLocationDetails, OperationName, Resource, ResourceDisplayName, ServicePrincipalName, Status, TokenIssuerName, TokenIssuerType, Type, UniqueTokenIdentifier, UserDisplayName, UserType

 

**************************************************************************

 

This query over Azure Active Directory sign-in considers all user sign-ins for each Azure Active

  Directory application and picks out the most anomalous change in location profile for a user within an

  individual application. The intent is to hunt for user account compromise, possibly via a specific application

  vector.

 

SigninLogs

  // Forces Log Analytics to recognize that the query should be run over full time range

  | extend  locationString= strcat(tostring(LocationDetails["countryOrRegion"]), "/",

  tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")

  | project TimeGenerated, AppDisplayName, UserPrincipalName, locationString

  // Create time series

  | make-series dLocationCount = dcount(locationString) on TimeGenerated step 1d

  by UserPrincipalName, AppDisplayName

  // Compute best fit line for each entry

  | extend (RSquare, Slope, Variance, RVariance, Interception, LineFit) = series_fit_line(dLocationCount)

  // Chart the 3 most interesting lines 

  // A 0-value slope corresponds to an account being completely stable over time for a given Azure Active Directory application

  | top 3 by Slope desc

  | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName

  //| render timechart

  | project AccountCustomEntity, TimeGenerated, AppDisplayName, UserPrincipalName

******************************************************************************

 

This query over Azure Active Directory sign-in considers all user sign-ins for each Azure Active

  Directory application and picks out the most anomalous change in location profile for a user within an

  individual application. The intent is to hunt for user account compromise, possibly via a specific application

  vector.

  This variation of the query joins the results back onto the original sign-in data to allow review of the

  location set with each identified user in tabular form.

 

SigninLogs

  // Forces Log Analytics to recognize that the query should be run over full time range

  | extend  locationString= strcat(tostring(LocationDetails["countryOrRegion"]), "/",

  tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")

  | project TimeGenerated, AppDisplayName , UserPrincipalName, locationString

  // Create time series

  | make-series dLocationCount = dcount(locationString) on TimeGenerated step 1d

  by UserPrincipalName, AppDisplayName

  // Compute best fit line for each entry

  | extend (RSquare,Slope,Variance,RVariance,Interception,LineFit)=series_fit_line(dLocationCount)

  // Chart the 3 most interesting lines 

  // A 0-value slope corresponds to an account being completely stable over time for a given Azure Active Directory application

  | top 3 by Slope desc 

  // Extract the set of locations for each top user:

  | join kind=inner (SigninLogs

  | extend  locationString= strcat(tostring(LocationDetails["countryOrRegion"]), "/",

  tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")

  | summarize locationList = makeset(locationString), threeDayWindowLocationCount=dcount(locationString) by AppDisplayName, UserPrincipalName,

  timerange=bin(TimeGenerated, 3d)) on AppDisplayName, UserPrincipalName

  | order by UserPrincipalName, timerange asc

  | project timerange, AppDisplayName , UserPrincipalName, threeDayWindowLocationCount, locationList

  | order by AppDisplayName, UserPrincipalName, timerange asc

  | extend timestamp = timerange, AccountCustomEntity = UserPrincipalName

  | where UserPrincipalName == Nledec@company.com

******************************************************************************

Attempts to sign in to disabled accounts by account name

description: |

  'Failed attempts to sign in to disabled accounts summarized by account name'

 

 

SigninLogs

  | where ResultType == "50057"

  | where ResultDescription == "User account is disabled. The account has been disabled by an administrator."

  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), count() by AppDisplayName, UserPrincipalName

  | extend timestamp = StartTime, AccountCustomEntity = UserPrincipalName

  | order by count_ desc

*********************************************************

 

Query for accounts seen signing in for the first time - these could be associated

  with stale/inactive accounts that ought to have been deleted but were not - and have

  subseuqently been compromised.

  Results for user accounts created in the last 7 days are filtered out'

 

let starttime = todatetime('{{StartTimeISO}}');

  let endtime = todatetime('{{EndTimeISO}}');

  let lookback = starttime - 14d;

  let midtime = starttime - 7d;

  SigninLogs

  | where TimeGenerated between(starttime..endtime)

  // successful sign-in

  | where ResultType == 0

  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), loginCountToday=count() by UserPrincipalName, Identity

  | join kind=leftanti (

     SigninLogs

     // historical successful sign-in

     | where TimeGenerated between(lookback..starttime)

     | where ResultType == 0

     | summarize by UserPrincipalName, Identity

  ) on UserPrincipalName

  | join kind= leftanti (

     // filter out newly created user accounts

     AuditLogs

     | where TimeGenerated between(midtime..starttime)

     | where OperationName == "Add user"

     // Normalize to lower case in order to match against equivalent UPN in Signin logs

     | extend NewUserPrincipalName = tolower(extractjson("$.userPrincipalName", tostring(TargetResources[0]), typeof(string)))

  ) on $left.UserPrincipalName == $right.NewUserPrincipalName

  | extend timestamp = StartTime, AccountCustomEntity = UserPrincipalName

 

********************************************************************************

 

Get the list of Teams sites that have federated external users. These users have a domain name and/or a UPN suffix that isn't owned by your organization.

 

OfficeActivity

| where TimeGenerated > ago(3d)

| where Operation =~ "MemberAdded"

| where parse_json(Members)[0].Role == 3

| project TeamName, Operation, UserId, Members

| mv-expand bagexpansion=array Members

| evaluate bag_unpack(Members)

//| where UserId == Nledec@company.com

 

*******************************************************************************

 

To help explain using where AppDisplayName has Teams further, the KQL you see below demonstrates a successful logon from one IP address with failure from a different IP address, but scoped to only Teams sign-ins:

 

let timeFrame = 1d;

let logonDiff = 10m;

SigninLogs

  | where TimeGenerated >= ago(timeFrame)

  | where ResultType == "0"

  | where AppDisplayName has "Teams"

  | project SuccessLogonTime = TimeGenerated, UserPrincipalName, SuccessIPAddress = IPAddress, AppDisplayName, SuccessIPBlock = strcat(split(IPAddress, ".")[0], ".", split(IPAddress, ".")[1])

  | join kind= inner (

      SigninLogs

      | where TimeGenerated >= ago(timeFrame)

      | where ResultType !in ("0", "50140")

      | where ResultDescription !~ "Other" 

      | where AppDisplayName startswith "Microsoft Teams"

      | project FailedLogonTime = TimeGenerated, UserPrincipalName, FailedIPAddress = IPAddress, AppDisplayName, ResultType, ResultDescription

  ) on UserPrincipalName, AppDisplayName

  | where SuccessLogonTime < FailedLogonTime and FailedLogonTime - SuccessLogonTime <= logonDiff and FailedIPAddress !startswith SuccessIPBlock

  | summarize FailedLogonTime = max(FailedLogonTime), SuccessLogonTime = max(SuccessLogonTime) by UserPrincipalName, SuccessIPAddress, AppDisplayName, FailedIPAddress, ResultType, ResultDescription

  | extend timestamp = SuccessLogonTime, AccountCustomEntity = UserPrincipalName, IPCustomEntity = SuccessIPAddress

 

*************************************************************************************

This query will help detect any Sign-in's from non-compliant device/device registered without MFA(Multi-factor Authentication)/unknown device to privileged account using pre-built watchlist to identify accounts.

  Microsoft Sentinel now provides built-in watchlist templates, that can be customized for your environment and used during investigations.

 

let priv_users = ('_GetWatchlist("VIPUsers")') ;

  (union isfuzzy=true

  (SigninLogs //when a device is registered/joined without MFA

  | where AuthenticationRequirement == "multiFactorAuthentication"

  | where ResourceDisplayName == "Device Registration Service"

  | where ConditionalAccessStatus == "success"

  | extend AccountName = tolower(split(UserPrincipalName, "@")[0]), WinSecEventDomain = "-"

  | where AccountName in (priv_users)

  | project-rename  ServiceOrSystem = AppDisplayName, ClientIP = IPAddress),

  (

  (SigninLogs //Sign-ins by non-compliant devices

  | where DeviceDetail.isCompliant == false

  | where ConditionalAccessStatus == "success"

    | extend AccountName = tolower(split(UserPrincipalName, "@")[0]), WinSecEventDomain = "-"

    | where AccountName in (priv_users)

    | project-rename  ServiceOrSystem = AppDisplayName, ClientIP = IPAddress)

  ),

  (SigninLogs //Sign-ins by unknown devices

  | where isempty(DeviceDetail.deviceId)

  | where AuthenticationRequirement == "singleFactorAuthentication"

  | where ResultType == "0"

  | where NetworkLocationDetails == "[]"

  | extend AccountName = tolower(split(UserPrincipalName, "@")[0]), WinSecEventDomain = "-"

  | where AccountName in (priv_users)

  | project-rename  ServiceOrSystem = AppDisplayName, ClientIP = IPAddress)

  )

  | project AccountCustomEntity = AccountName, AppId, Category, IPCustomEntity = ClientIP, CorrelationId, ResourceCustomEntity = ResourceId, Identity

 

**********************************************************************************

Access attempts to Azure Portal from an unauthorized user. Either invalid password or the user account does not exist.'

 

SigninLogs

  | where AppDisplayName contains "Azure Portal"

  // 50126 - Invalid username or password, or invalid on-premises username or password.

  // 50020? - The user doesn't exist in the tenant.

  | where ResultType in ( "50126" , "50020")

  | extend OS = DeviceDetail.operatingSystem, Browser = DeviceDetail.browser

  | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails)

  | extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city)

  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), IPAddresses = makeset(IPAddress), DistinctIPCount = dcount(IPAddress),

  makeset(OS), makeset(Browser), makeset(City), AttemptCount = count()

  by UserDisplayName, UserPrincipalName, AppDisplayName, ResultType, ResultDescription, StatusCode, StatusDetails, Location, State

  | extend timestamp = StartTime, AccountCustomEntity = UserPrincipalName

  | sort by AttemptCount

********************************************************************************

name: Anomalous Azure Active Directory apps based on authentication location

description: |

  'This query over Azure AD sign-in activity highlights Azure AD apps with

  an unusually high ratio of distinct geolocations versus total number of authentications'

 

let azureSignIns =

  SigninLogs

  | where SourceSystem == "Azure AD"

  | where OperationName == "Sign-in activity"

  | project TimeGenerated, OperationName, AppDisplayName , Identity, UserId, UserPrincipalName, Location, LocationDetails,

  ClientAppUsed, DeviceDetail, ConditionalAccessPolicies;

  azureSignIns

  | extend locationString = strcat(tostring(LocationDetails["countryOrRegion"]), "/",

  tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";" , tostring(LocationDetails["geoCoordinates"]))

  | summarize rawSigninCount = count(), countByAccount = dcount(UserId), locationCount = dcount(locationString) by AppDisplayName

  // tail - pick a threshold to rule out the very-high volume Azure AD apps

  | where rawSigninCount < 1000

  // more locations than accounts

  | where locationCount>countByAccount

  // almost as many / more locations than sign-ins!

  | where 1.0*rawSigninCount / locationCount > 0.8

  | order by rawSigninCount  desc

  | join kind = leftouter (

     azureSignIns

  ) on AppDisplayName

  | project AppDisplayName, TimeGenerated , Identity, rawSigninCount, countByAccount, locationCount, 

  locationString = strcat(tostring(LocationDetails["countryOrRegion"]), "/", tostring(LocationDetails["state"]), "/",

  tostring(LocationDetails["city"]), ";" , tostring(LocationDetails["geoCoordinates"])), UserPrincipalName, ClientAppUsed, DeviceDetail, Location, LocationDetails, OperationName, UserId

  | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName

  | order by AppDisplayName, TimeGenerated desc

  | project TimeGenerated, AppDisplayName, countByAccount, Identity, locationCount, UserPrincipalName, ClientAppUsed, DeviceDetail, Location, LocationDetails, OperationName, UserId