SOLVED

Log-Analytics query doesn't show results with join operator

%3CLINGO-SUB%20id%3D%22lingo-sub-2201547%22%20slang%3D%22en-US%22%3ELog-Analytics%20query%20doesn't%20show%20results%20with%20join%20operator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2201547%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20with%20a%20query%2C%20which%20will%20be%20used%20for%20a%20(Sentinel)%20analytics%20rule.%20The%20purpose%20of%20this%20alert%20rule%20is%20to%20check%20for%20logons%20to%20disabled%20accounts%20in%20the%20last%20day%20and%20only%20show%20results%20when%20that%20account%20was%20disabled%20%26gt%3B30%20days%20ago.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I've%20made%20so%20far%3A%3C%2FP%3E%3CP%3Elet%20DisabledAccount%20%3D%20SecurityEvent%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Ewhere%3C%2FFONT%3E%20EventID%20%3D%3D%20%3CFONT%20color%3D%22%23FF0000%22%3E%224725%22%3C%2FFONT%3E%20%3CFONT%20color%3D%22%2399CC00%22%3E%2F%2F%20EventID%204725%20%3D%20User%20disabled%20in%20AD%3C%2FFONT%3E%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Ewhere%3C%2FFONT%3E%20TimeGenerated%20%26gt%3B%20ago(%3CFONT%20color%3D%22%23339966%22%3E30%3C%2FFONT%3Ed)%20%3CFONT%20color%3D%22%2399CC00%22%3E%2F%2F%20Disabling%20of%20account%20should%20be%20more%20than%2030%20days%20ago.%3C%2FFONT%3E%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Ewhere%3C%2FFONT%3E%20SubjectUserName%20!%3CFONT%20color%3D%22%233366FF%22%3Eendswith%3C%2FFONT%3E%20%3CFONT%20color%3D%22%23FF0000%22%3E%22%24%22%3C%2FFONT%3E%20%3CFONT%20color%3D%22%233366FF%22%3Eand%3C%2FFONT%3E%20TargetUserName%20!%3CFONT%20color%3D%22%233366FF%22%3Eendswith%3C%2FFONT%3E%20%3CFONT%20color%3D%22%23FF0000%22%3E%22%24%22%3C%2FFONT%3E%20%3CFONT%20color%3D%22%2399CC00%22%3E%2F%2F%20Filter%20out%20share%20accounts.%3C%2FFONT%3E%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Eproject%3C%2FFONT%3E%20DisabledOnDate%20%3D%20TimeGenerated%2C%20TargetUserName%2C%20UserDisabledBy%20%3D%20SubjectUserName%20%3B%3CBR%20%2F%3Elet%20LogonWithDisabledAccount%20%3D%20SecurityEvent%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Ewhere%3C%2FFONT%3E%20TimeGenerated%20%26gt%3B%20ago(%3CFONT%20color%3D%22%23339966%22%3E1%3C%2FFONT%3Ed)%20%3CFONT%20color%3D%22%2399CC00%22%3E%2F%2F%20Logon%20with%20disabled%20account%20should%20be%20in%20the%20last%201%20day.%3C%2FFONT%3E%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Ewhere%3C%2FFONT%3E%20EventID%20%3D%3D%20%3CFONT%20color%3D%22%23FF0000%22%3E%224768%22%3C%2FFONT%3E%20%3CFONT%20color%3D%22%233366FF%22%3Eand%3C%2FFONT%3E%20Status%20%3CFONT%20color%3D%22%233366FF%22%3Econtains%3C%2FFONT%3E%20%3CFONT%20color%3D%22%23FF0000%22%3E%220x12%22%3C%2FFONT%3E%20%3CFONT%20color%3D%22%2399CC00%22%3E%2F%2F%20EventID%204768%20%3D%20logon%20on%20disabled%20account%3C%2FFONT%3E%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Ewhere%3C%2FFONT%3E%20SubjectUserName%20!%3CFONT%20color%3D%22%233366FF%22%3Eendswith%3C%2FFONT%3E%20%3CFONT%20color%3D%22%23FF0000%22%3E%22%24%22%3C%2FFONT%3E%20%3CFONT%20color%3D%22%2399CC00%22%3E%2F%2F%20Filter%20out%20share%20accounts.%3C%2FFONT%3E%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Eproject%3C%2FFONT%3E%20LogonTime%20%3D%20TimeGenerated%2C%20TargetUserName%2C%20Observer%20%3D%20Computer%20%3B%3CBR%20%2F%3EDisabledAccount%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Ejoin%3C%2FFONT%3E%20(%20LogonWithDisabledAccount%20)%20%3CFONT%20color%3D%22%233366FF%22%3Eon%3C%2FFONT%3E%20TargetUserName%3CBR%20%2F%3E%7C%20%3CFONT%20color%3D%22%233366FF%22%3Eproject%3C%2FFONT%3E%20LogonTime%2C%20TargetUserName%2C%20UserDisabledBy%2C%20DisabledOnDate%2C%20Observer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20running%20each%20of%20the%20sections%20of%20this%20code%20and%20they%20give%20back%20results.%20But%20once%20I%20run%20the%20whole%20query%2C%20including%20the%20join%2C%20it%20gives%20back%20that%20no%20results%20have%20been%20found%20for%20the%20selected%20timerange%2C%20which%20is%20set%20to%20%22In%20query%22%20by%20the%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20out%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2201547%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELog%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2204802%22%20slang%3D%22en-US%22%3ERe%3A%20Log-Analytics%20query%20doesn't%20show%20results%20with%20join%20operator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2204802%22%20slang%3D%22en-US%22%3EIn%20a%20Analytics%20rule%2C%20you%20can%20go%20back%2014days%2C%20not%2030day%20-%20it%20will%20run%20in%20a%20normal%20logs%20window%2C%20as%20that%20doesn't%20have%20this%20setting.%20%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Ftutorial-detect-threats-custom%23query-scheduling-and-alert-threshold%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Ftutorial-detect-threats-custom%23query-scheduling-and-alert-threshold%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3Ealso%20-%20Tiander%20did%20a%20great%20webcast%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FG6TIzJK8XBA%3Ft%3D3152%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FG6TIzJK8XBA%3Ft%3D3152%3C%2FA%3E%20%E2%80%93%20watch%20it%20all%20%3Asmiling_face_with_smiling_eyes%3A%2C%20but%20%E2%80%9C14days%20use%20case%E2%80%9D%20starts%20at%2042min%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi all,

 

I need help with a query, which will be used for a (Sentinel) analytics rule. The purpose of this alert rule is to check for logons to disabled accounts in the last day and only show results when that account was disabled >30 days ago.

 

This is what I've made so far:

let DisabledAccount = SecurityEvent
| where EventID == "4725" // EventID 4725 = User disabled in AD
| where TimeGenerated > ago(30d) // Disabling of account should be more than 30 days ago.
| where SubjectUserName !endswith "$" and TargetUserName !endswith "$" // Filter out share accounts.
| project DisabledOnDate = TimeGenerated, TargetUserName, UserDisabledBy = SubjectUserName ;
let LogonWithDisabledAccount = SecurityEvent
| where TimeGenerated > ago(1d) // Logon with disabled account should be in the last 1 day.
| where EventID == "4768" and Status contains "0x12" // EventID 4768 = logon on disabled account
| where SubjectUserName !endswith "$" // Filter out share accounts.
| project LogonTime = TimeGenerated, TargetUserName, Observer = Computer ;
DisabledAccount
| join ( LogonWithDisabledAccount ) on TargetUserName
| project LogonTime, TargetUserName, UserDisabledBy, DisabledOnDate, Observer

 

I've tried running each of the sections of this code and they give back results. But once I run the whole query, including the join, it gives back that no results have been found for the selected timerange, which is set to "In query" by the way.

 

Can you help me out? 

1 Reply
best response confirmed by stanleyk1 (Occasional Visitor)
Solution
In a Analytics rule, you can go back 14days, not 30day - it will run in a normal logs window, as that doesn't have this setting.
https://docs.microsoft.com/en-us/azure/sentinel/tutorial-detect-threats-custom#query-scheduling-and-...


also - Tiander did a great webcast here: https://youtu.be/G6TIzJK8XBA?t=3152 – watch it all :smiling_face_with_smiling_eyes:, but “14days use case” starts at 42min