SOLVED

Conflict with custom time range when running scheduled Sentinel Alert - identityinfo

%3CLINGO-SUB%20id%3D%22lingo-sub-2846379%22%20slang%3D%22en-US%22%3EConflict%20with%20custom%20time%20range%20when%20running%20scheduled%20Sentinel%20Alert%20-%20identityinfo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2846379%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20team.%3CBR%20%2F%3EI'm%20working%20with%20Sentinel%20to%20create%20a%20custom%20alert%20rule%20in%20attempt%20to%20reduce%20the%20noise%20generated%20by%20false%20positives.%20I've%20went%20ahead%20and%20modified%20the%20out-of-the-box%20alert%20for%20%22RDP%20Nesting%22%20and%20leftanti%20joined%20the%20table%20%22Identityinfo%22%20to%20compare%20users'%20last%20names%20from%20the%20UPN%20in%20Azure%20AD%20to%20their%20non-federated%20identities%20associated%20with%20with%20VM%20signins%20by%20leveraging%20regex.%20Since%20users%20that%20sign%20into%20VMs%20everyday%20are%20not%20also%20signing%20in%20to%20Azure%20everyday%2C%20I%20had%20to%20set%20the%20time%20range%20search%20for%20Identity%20info%20to%2090%20days.%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20the%20issue.%20When%20I%20run%20the%20KQL%20query%20pasted%20below%2C%20the%20query%20works%20as%20intended%2C%20only%20returning%20users%20not%20associated%20with%20the%20specified%20AAD%20Group%20performing%20this%20action.%20The%20issue%20comes%20when%20I%20enable%20it%20as%20an%20alert%20in%20Sentinel.%20It%20seems%20that%20the%20%22Set%20query_now%20%3D%22%20is%20also%20overriding%20%22timegenerated%22%20in%20identityinfo%20table%20and%20changing%20the%20expected%20results%20of%20the%20query.%20Any%20thoughts%20on%20how%20I%20might%20go%20about%20resolving%3F%20See%20query%20below%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%2F%2F%20The%20query_now%20parameter%20represents%20the%20time%20(in%20UTC)%20at%20which%20the%20scheduled%20analytics%20rule%20ran%20to%20produce%20this%20alert.%0Aset%20query_now%20%3D%20datetime(2021-10-14T14%3A48%3A21.0595216Z)%3B%0Alet%20endtime%20%3D%201d%3B%0Alet%20starttime%20%3D%208d%3B%0A%2F%2F%20The%20threshold%20below%20excludes%20matching%20on%20RDP%20connection%20computer%20counts%20of%205%20or%20more%20by%20a%20given%20account%20and%20IP%20in%20a%20given%20day.%20%20Change%20the%20threshold%20as%20needed..%0Alet%20threshold%20%3D%205%3B%0ASecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(endtime)%20%0A%7C%20where%20EventID%20%3D%3D%204624%20and%20LogonType%20%3D%3D%2010%0A%2F%2F%20Labeling%20the%20first%20RDP%20connection%20time%2C%20computer%20and%20ip%0A%7C%20extend%0A%20%20%20%20FirstHop%20%3D%20TimeGenerated%2C%0A%20%20%20%20FirstComputer%20%3D%20toupper(Computer)%2C%0A%20%20%20%20FirstIPAddress%20%3D%20IpAddress%2C%0A%20%20%20%20Account%20%3D%20tolower(Account)%20%20%0A%7C%20join%20kind%3Dinner%20(%0A%20%20%20%20SecurityEvent%0A%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(endtime)%20%0A%20%20%20%20%7C%20where%20EventID%20%3D%3D%204624%20and%20LogonType%20%3D%3D%2010%0A%20%20%20%20%2F%2F%20Labeling%20the%20second%20RDP%20connection%20time%2C%20computer%20and%20ip%0A%20%20%20%20%7C%20extend%0A%20%20%20%20%20%20%20%20SecondHop%20%3D%20TimeGenerated%2C%0A%20%20%20%20%20%20%20%20SecondComputer%20%3D%20toupper(Computer)%2C%0A%20%20%20%20%20%20%20%20SecondIPAddress%20%3D%20IpAddress%2C%0A%20%20%20%20%20%20%20%20Account%20%3D%20tolower(Account)%0A%20%20%20%20)%0A%20%20%20%20on%20Account%0A%2F%2F%20Make%20sure%20that%20the%20first%20connection%20is%20after%20the%20second%20connection%20--%26gt%3B%20SecondHop%20%26gt%3B%20FirstHop%0A%2F%2F%20Then%20identify%20only%20RDP%20to%20another%20computer%20from%20within%20the%20first%20RDP%20connection%20by%20only%20choosing%20matches%20where%20the%20Computer%20names%20do%20not%20match%20--%26gt%3B%20FirstComputer%20!%3D%20SecondComputer%0A%2F%2F%20Then%20make%20sure%20the%20IPAddresses%20do%20not%20match%20by%20excluding%20connections%20from%20the%20same%20computers%20with%20first%20hop%20RDP%20connections%20to%20multiple%20computers%20--%26gt%3B%20FirstIPAddress%20!%3D%20SecondIPAddress%0A%7C%20where%20FirstComputer%20!%3D%20SecondComputer%0A%20%20%20%20and%20FirstIPAddress%20!%3D%20SecondIPAddress%0A%20%20%20%20and%20SecondHop%20%26gt%3B%20FirstHop%0A%2F%2F%20where%20the%20second%20hop%20occurs%20within%2030%20minutes%20of%20the%20first%20hop%0A%7C%20where%20SecondHop%20%26lt%3B%3D%20FirstHop%20%2B%2030m%0A%7C%20distinct%0A%20%20%20%20Account%2C%0A%20%20%20%20FirstHop%2C%0A%20%20%20%20FirstComputer%2C%0A%20%20%20%20FirstIPAddress%2C%0A%20%20%20%20SecondHop%2C%0A%20%20%20%20SecondComputer%2C%0A%20%20%20%20SecondIPAddress%2C%0A%20%20%20%20AccountType%2C%0A%20%20%20%20Activity%2C%0A%20%20%20%20LogonTypeName%2C%0A%20%20%20%20ProcessName%0A%2F%2F%20use%20left%20anti%20to%20exclude%20anything%20from%20the%20previous%207%20days%20where%20the%20Account%20and%20IP%20has%20connected%205%20or%20more%20computers.%0A%7C%20join%20kind%3Dleftanti%20(%0A%20%20%20%20SecurityEvent%0A%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(starttime)%20and%20TimeGenerated%20%26lt%3B%20ago(endtime)%20%0A%20%20%20%20%7C%20where%20EventID%20%3D%3D%204624%20and%20LogonType%20%3D%3D%2010%0A%20%20%20%20%7C%20summarize%20makeset(Computer)%2C%20ComputerCount%20%3D%20dcount(Computer)%20by%20bin(TimeGenerated%2C%201d)%2C%20Account%20%3D%20tolower(Account)%2C%20IpAddress%0A%20%20%20%20%2F%2F%20Connection%20count%20to%20computer%20by%20same%20account%20and%20IP%20to%20exclude%20counts%20of%205%20or%20more%20on%20a%20given%20day%0A%20%20%20%20%7C%20where%20ComputerCount%20%26gt%3B%3D%20threshold%0A%20%20%20%20%7C%20mvexpand%20set_Computer%0A%20%20%20%20%7C%20extend%20Computer%20%3D%20toupper(set_Computer)%0A%20%20%20%20)%0A%20%20%20%20on%0A%20%20%20%20Account%2C%0A%20%20%20%20%24left.SecondComputer%20%3D%3D%20%24right.Computer%2C%0A%20%20%20%20%24left.SecondIPAddress%20%3D%3D%20%24right.IpAddress%0A%7C%20summarize%20FirstHopFirstSeen%20%3D%20min(FirstHop)%2C%20FirstHopLastSeen%20%3D%20max(FirstHop)%0A%20%20%20%20by%20Account%2C%20FirstComputer%2C%20FirstIPAddress%2C%20SecondHop%2C%20SecondComputer%2C%20%0A%20%20%20%20SecondIPAddress%2C%20AccountType%2C%20Activity%2C%20LogonTypeName%2C%20ProcessName%0A%7C%20extend%20timestamp%20%3D%20FirstHopFirstSeen%2C%20AccountCustomEntity%20%3D%20Account%2C%20HostCustomEntity%20%3D%20FirstComputer%2C%20IPCustomEntity%20%3D%20FirstIPAddress%2C%0A%20%20%20%20RegexLN%20%3D%20tostring%20(substring%20((split(Account%2C%20'%5C%5C')%5B-1%5D)%2C%201))%0A%7C%20project%20Account%2C%20FirstComputer%2C%20FirstIPAddress%2C%20SecondHop%2C%20SecondComputer%2C%20RegexLN%2C%0A%20%20%20%20SecondIPAddress%2C%20AccountType%2C%20Activity%2C%20LogonTypeName%2C%20ProcessName%2C%20timestamp%20%3D%20FirstHopFirstSeen%2C%20AccountCustomEntity%20%3D%20Account%2C%20HostCustomEntity%20%3D%20FirstComputer%2C%20IPCustomEntity%20%3D%20FirstIPAddress%0A%7C%20extend%20AccountLastName%20%3D%20tostring%20(substring%20((split(RegexLN%2C%20'.')%5B-1%5D)%2C%200))%20%20%20%0A%2F%2Fuse%20left%20anti%20to%20return%20useraccounts%20that%20have%20no%20matched%20lastname%20within%20Azure%20AD%20Group%20for%20Company%20Read%20Only%20Subscription%20and%20are%20performing%20rdp%20actions%0A%7C%20join%20kind%3Dleftanti%20(%0A%20%20%20%20IdentityInfo%0A%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20ago%20(90d)%0A%20%20%20%20%7C%20where%20AccountName%20contains%20%22%22%0A%20%20%20%20%7C%20where%20GroupMembership%20contains%20%22Company%20Read%20Only%20Subscription%22%0A%20%20%20%20%7C%20extend%20AccountLastName%20%3D%20tostring%20(split(AccountName%2C%20'.')%5B-1%5D)%0A%20%20%20%20)%0A%20%20%20%20on%20AccountLastName%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2846379%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EActivity%20Logs%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAlerts%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELog%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2849363%22%20slang%3D%22en-US%22%3ERe%3A%20Conflict%20with%20custom%20time%20range%20when%20running%20scheduled%20Sentinel%20Alert%20-%20identityinfo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2849363%22%20slang%3D%22en-US%22%3EThere%20is%20a%20dedicated%20Sentinel%20channel.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fbd-p%2FAzureSentinel%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fbd-p%2FAzureSentinel%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EAzure%20Sentinel%20rules%20have%20a%20max%20look%20back%20of%2014days%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Fdetect-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%2Fdetect-threats-custom%23query-scheduling-and-alert-threshold%3C%2FA%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20is%20a%20workaround%20(if%20you%20really%20need%20it).%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
New Contributor

Hi team.
I'm working with Sentinel to create a custom alert rule in attempt to reduce the noise generated by false positives. I've went ahead and modified the out-of-the-box alert for "RDP Nesting" and leftanti joined the table "Identityinfo" to compare users' last names from the UPN in Azure AD to their non-federated identities associated with with VM signins by leveraging regex. Since users that sign into VMs everyday are not also signing in to Azure everyday, I had to set the time range search for Identity info to 90 days.

Here is the issue. When I run the KQL query pasted below, the query works as intended, only returning users not associated with the specified AAD Group performing this action. The issue comes when I enable it as an alert in Sentinel. It seems that the "Set query_now =" is also overriding "timegenerated" in identityinfo table and changing the expected results of the query. Any thoughts on how I might go about resolving? See query below:


 

 

let endtime = 1d;
let starttime = 8d;
// The threshold below excludes matching on RDP connection computer counts of 5 or more by a given account and IP in a given day.  Change the threshold as needed..
let threshold = 5;
SecurityEvent
| where TimeGenerated >= ago(endtime) 
| where EventID == 4624 and LogonType == 10
// Labeling the first RDP connection time, computer and ip
| extend
    FirstHop = TimeGenerated,
    FirstComputer = toupper(Computer),
    FirstIPAddress = IpAddress,
    Account = tolower(Account)  
| join kind=inner (
    SecurityEvent
    | where TimeGenerated >= ago(endtime) 
    | where EventID == 4624 and LogonType == 10
    // Labeling the second RDP connection time, computer and ip
    | extend
        SecondHop = TimeGenerated,
        SecondComputer = toupper(Computer),
        SecondIPAddress = IpAddress,
        Account = tolower(Account)
    )
    on Account
// Make sure that the first connection is after the second connection --> SecondHop > FirstHop
// Then identify only RDP to another computer from within the first RDP connection by only choosing matches where the Computer names do not match --> FirstComputer != SecondComputer
// Then make sure the IPAddresses do not match by excluding connections from the same computers with first hop RDP connections to multiple computers --> FirstIPAddress != SecondIPAddress
| where FirstComputer != SecondComputer
    and FirstIPAddress != SecondIPAddress
    and SecondHop > FirstHop
// where the second hop occurs within 30 minutes of the first hop
| where SecondHop <= FirstHop + 30m
| distinct
    Account,
    FirstHop,
    FirstComputer,
    FirstIPAddress,
    SecondHop,
    SecondComputer,
    SecondIPAddress,
    AccountType,
    Activity,
    LogonTypeName,
    ProcessName
// use left anti to exclude anything from the previous 7 days where the Account and IP has connected 5 or more computers.
| join kind=leftanti (
    SecurityEvent
    | where TimeGenerated >= ago(starttime) and TimeGenerated < ago(endtime) 
    | where EventID == 4624 and LogonType == 10
    | summarize makeset(Computer), ComputerCount = dcount(Computer) by bin(TimeGenerated, 1d), Account = tolower(Account), IpAddress
    // Connection count to computer by same account and IP to exclude counts of 5 or more on a given day
    | where ComputerCount >= threshold
    | mvexpand set_Computer
    | extend Computer = toupper(set_Computer)
    )
    on
    Account,
    $left.SecondComputer == $right.Computer,
    $left.SecondIPAddress == $right.IpAddress
| summarize FirstHopFirstSeen = min(FirstHop), FirstHopLastSeen = max(FirstHop)
    by Account, FirstComputer, FirstIPAddress, SecondHop, SecondComputer, 
    SecondIPAddress, AccountType, Activity, LogonTypeName, ProcessName
| extend timestamp = FirstHopFirstSeen, AccountCustomEntity = Account, HostCustomEntity = FirstComputer, IPCustomEntity = FirstIPAddress,
    RegexLN = tostring (substring ((split(Account, '\\')[-1]), 1))
| project Account, FirstComputer, FirstIPAddress, SecondHop, SecondComputer, RegexLN,
    SecondIPAddress, AccountType, Activity, LogonTypeName, ProcessName, timestamp = FirstHopFirstSeen, AccountCustomEntity = Account, HostCustomEntity = FirstComputer, IPCustomEntity = FirstIPAddress
| extend AccountLastName = tostring (substring ((split(RegexLN, '.')[-1]), 0))   
//use left anti to return useraccounts that have no matched lastname within Azure AD Group for Company Read Only Subscription and are performing rdp actions
| join kind=leftanti (
    IdentityInfo
    | where TimeGenerated > ago (90d)
    | where AccountName contains ""
    | where GroupMembership contains "Company Read Only Subscription"
    | extend AccountLastName = tostring (split(AccountName, '.')[-1])
    )
    on AccountLastName

 

 

 

2 Replies
best response confirmed by Clive Watson (Microsoft)
Solution
There is a dedicated Sentinel channel. https://techcommunity.microsoft.com/t5/azure-sentinel/bd-p/AzureSentinel

Azure Sentinel rules have a max look back of 14days https://docs.microsoft.com/en-us/azure/sentinel/detect-threats-custom#query-scheduling-and-alert-thr...

There is a workaround (if you really need it). 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
I think I got it working by adjusting the max look back from 8 to 14 days.
Will need to keep an eye on it.
Thanks.