let lookback	 = 1h;
let historyStart = ago(29d);
let historyEnd   = ago(lookback);
let ExcludedApps = dynamic([
	"Windows Sign In",
	"Viva Engage"
]);
// Vorfilter: nur CorrelationIds mit mind. 2 verschiedenen UserAgents
let MultiUASessions =
	AADNonInteractiveUserSignInLogs
	| where TimeGenerated >= ago(lookback)
	| where ResultType == 0
	| where isnotempty(UserAgent)
	| where isnotempty(CorrelationId)
	| summarize UACount_pre = dcount(UserAgent) by CorrelationId
	| where UACount_pre >= 2
	| project CorrelationId;
let SuspiciousSessions =
	AADNonInteractiveUserSignInLogs
	| where TimeGenerated >= ago(lookback)
	| where ResultType == 0
	| where isnotempty(UserAgent)
	| where isnotempty(CorrelationId)
	// Nur Sessions mit mind. 2 UAs weiterverarbeiten
	| join kind=inner hint.strategy=broadcast (MultiUASessions) on CorrelationId
	| extend UAType = case(
		UserAgent has "MSAL",									   "MSAL-Client",
		UserAgent has "Windows-AzureAD-Authentication-Provider",	"Windows-Auth-Provider",
		UserAgent has "Dalvik",									 "Android-App",
		UserAgent has "CFNetwork",								  "iOS-App",
		UserAgent has "Microsoft Authenticator",					"Authenticator-App",
		UserAgent has_any ("python", "curl", "powershell",
						   "okhttp", "axios", "go-http",
						   "java/", "requests", "wget"),			"Suspicious-Tool",
		UserAgent startswith "Mozilla",							 "Browser",
																	"Unknown"
	  )
	| extend UANormalized = replace_regex(UserAgent, @'\d+\.\d+\.\d+\.\d+', "x.x.x.x")
	| extend UANormalized = replace_regex(UANormalized, @';\s*WebView/[\d\.]+', "")
	| summarize
		UATypes	 = make_set(UAType, 6),
		UAList	  = make_set(UANormalized, 6),
		IPList	  = make_set(IPAddress, 10),
		AppList	 = make_set(AppDisplayName, 5),
		FirstSeen   = min(TimeGenerated),
		LastSeen	= max(TimeGenerated),
		SignInCount = count()
		by UserPrincipalName, CorrelationId
	| extend UACount		   = array_length(UAList)
	| extend IPCount		   = array_length(IPList)
	| extend HasSuspiciousTool = UATypes has "Suspicious-Tool"
	| extend HasBrowser		= UATypes has "Browser"
	| extend IsUASuspicious	= case(
		HasSuspiciousTool == true,	  true,
		HasBrowser and UACount >= 2,	true,
										false
	  )
	| extend IsMixedLegitimate = (
		UATypes has "MSAL-Client" or
		UATypes has "Android-App" or
		UATypes has "iOS-App" or
		UATypes has "Authenticator-App"
	  ) and not(HasSuspiciousTool) and not(HasBrowser)
	| extend SessionDurationMin = datetime_diff('minute', LastSeen, FirstSeen)
	| extend AllAppsExcluded = array_length(set_difference(AppList, ExcludedApps)) == 0
	| where IsUASuspicious == true
	| where not(IsMixedLegitimate)
	| where SessionDurationMin <= 60
	| where not(AllAppsExcluded)
	| where not(
		UATypes has "Windows-Auth-Provider"
		and UATypes has "Browser"
		and UACount == 2
		and AppList has "Windows Sign In"
	  )
	| where not(
		UATypes has "Windows-Auth-Provider"
		and UATypes has "Authenticator-App"
		and not(HasSuspiciousTool)
	  );
let SuspiciousUsers = SuspiciousSessions
	| distinct UserPrincipalName;
let HistoricalIPs =
	EntraIdSignInEvents
	| where Timestamp >= historyStart and Timestamp < historyEnd
	| where ErrorCode == 0
	| summarize hint.shufflekey=AccountUpn
		IPSeenCount   = count(),
		HistoricalIPs = make_set(IPAddress, 200),
		ReportId	  = max(ReportId)
		by AccountUpn, IPAddress
	| where AccountUpn in (SuspiciousUsers);
SuspiciousSessions
| mv-expand CurrentIP = IPList to typeof(string)
| join hint.strategy=broadcast kind=leftouter (HistoricalIPs)
	on $left.UserPrincipalName == $right.AccountUpn
	and $left.CurrentIP == $right.IPAddress
| extend IPSeenBefore = isnotempty(IPSeenCount)
| extend IPSeenCount  = coalesce(todouble(IPSeenCount), 0.0)
| summarize
	UATypes			= any(UATypes),
	UACount			= any(UACount),
	UAList			 = any(UAList),
	IPList			 = any(IPList),
	IPCount			= any(IPCount),
	AppList			= any(AppList),
	FirstSeen		  = any(FirstSeen),
	LastSeen		   = any(LastSeen),
	SignInCount		= any(SignInCount),
	HasSuspiciousTool  = any(HasSuspiciousTool),
	HasBrowser		 = any(HasBrowser),
	IsUASuspicious	 = any(IsUASuspicious),
	SessionDurationMin = any(SessionDurationMin),
	ReportId		   = any(ReportId),
	NewIPCount		 = countif(IPSeenBefore == false),
	MinIPSeenCount	 = min(IPSeenCount),
	MaxIPSeenCount	 = max(IPSeenCount)
	by UserPrincipalName, CorrelationId
| extend WorstIPRisk = case(
	NewIPCount > 0,		 "High - new IP for this User",
	MinIPSeenCount < 3,	 "Medium - rare IP",
							"Low - known IP"
  )
| extend Severity = case(
	HasSuspiciousTool and NewIPCount > 0,			   "High",
	IsUASuspicious and WorstIPRisk startswith "High",   "High",
	IsUASuspicious and WorstIPRisk startswith "Medium", "Medium",
														"Low"
  )
| where Severity in ("High", "Medium")
| where MaxIPSeenCount < 2
| extend TimeGenerated = now()
| project
	TimeGenerated,
	Severity,
	UserPrincipalName,
	CorrelationId,
	ReportId,
	UACount,
	UATypes,
	UAList,
	HasSuspiciousTool,
	SessionDurationMin,
	NewIPCount,
	IPCount,
	IPList,
	MinIPSeenCount,
	MaxIPSeenCount,
	WorstIPRisk,
	AppList,
	FirstSeen,
	LastSeen,
	SignInCount
| sort by Severity asc, NewIPCount desc