SOLVED

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

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 CliveWatson (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.