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
