invalid formatting for analytics rule "Multiple RDP connections from Single System"

[HoGerl]

Describe the bug
actually the rule does not work like expected, since the account mapping does not work, if there are upper cases in it.

To Reproduce
Steps to reproduce the behavior:

1. just adding a tolower(account) on two lines display expected results.

lines to fix:

• https://github.com/Azure/Azure-Sentinel/blob/e74e14f51a66160af61f8c2f0791e473c3be7295/Detections/SecurityEvent/RDP_MultipleConnectionsFromSingleSystem.yaml#L49C44-L49C44
• https://github.com/Azure/Azure-Sentinel/blob/e74e14f51a66160af61f8c2f0791e473c3be7295/Detections/SecurityEvent/RDP_MultipleConnectionsFromSingleSystem.yaml#L35C16-L35C16

Expected behavior
the account mapping works

Screenshots
none.

Additional context
[related analytics rule] (https://github.com/Azure/Azure-Sentinel/blob/master/Detections/SecurityEvent/RDP_MultipleConnectionsFromSingleSystem.yaml)

[github-actions]

Thank you for submitting an Issue to the Azure Sentinel GitHub repo! You should expect an initial response to your Issue from the team within 5 business days. Note that this response may be delayed during holiday periods. For urgent, production-affecting issues please raise a support ticket via the Azure Portal.

[v-muuppugund]

Hi @HoGerl , Thanks for flagging this issue, we will investigate this issue and get back to you with some updates by 17Nov23. Thanks

[v-muuppugund]

Hi @HoGerl ,I am having the following queries i.e. we are using project/extend /summarize on account & join on account and we don't have exact data to replicate the issue, Could you please share screen shot of the no records for the query and also share sample data, so we check the query for both the tables i.e. security event and windows event.

[v-muuppugund]

Hi @HoGerl , Gentle Reminder: We are waiting for your response on this issue, could you please share the details of the above queries, Thanks.

[v-muuppugund]

Hi @HoGerl , Gentle Reminder: We are waiting for your response on this issue. If you still need to keep this issue active, please respond to it in the next 2 days i.e. 25Nov23. If we don't receive response by the given date, we will close this issue.

[kllnbrnjn]

Hi @v-muuppugund, maybe i can help out here, since we are experiencing the same issues.

To address this, i crafted a custom KQL datatable with pseudo data from the "SecurityEvent" table.

let CustomEvent = (datatable (
    TimeGenerated: datetime,
    Account: string,
    AccountType: string,
    Computer: string,
    EventSourceName: string,
    Channel: string,
    Task: int,
    Level: int,
    EventID: int,
    Activity: string,
    AuthenticationPackageName: string,
    ElevatedToken: string,
    ImpersonationLevel: string,
    IpAddress: string,
    IpPort: int,
    KeyLength: int,
    LmPackageName: string,
    LogonGuid: string,
    LogonProcessName: string,
    LogonType: int,
    LogonTypeName: string,
    Process: string,
    ProcessId: string,
    ProcessName: string,
    RestrictedAdminMode: string,
    SubjectAccount: string,
    SubjectDomainName: string,
    SubjectLogonId: string,
    SubjectUserName: string,
    SubjectUserSid: string,
    TargetAccount: string,
    TargetDomainName: string,
    TargetLinkedLogonId: string,
    TargetLogonId: string,
    TargetOutboundDomainName: string,
    TargetOutboundUserName: string,
    TargetUserName: string,
    TargetUserSid: string,
    TransmittedServices: string,
    VirtualAccount: string,
    WorkstationName: string,
    SourceComputerId: string,
    EventOriginId: string,
    TimeCollected: datetime,
    Type: string
) [ 
    datetime(2023-10-5), "UpperCaseAccount", "User", "TestComputer1", "TestSource", "Security", "0000", "0", "4624", "4624 - An account was successfully logged on.", "Kerberos", "000", "000", "127.0.0.1", 1234, 0, "-", "-", "Kerberos", 10, "10 - RemoteInteractive", "-", "0x0", "-", "-", "-\\-", "-", "0x0", "-", "-", "UpperCaseAccount", "domain.net", "0x0", "0x0", "-", "-", "UpperCaseAccount", "-", "-", "-", "-", "-", "-", datetime(2023-10-5), "SecurityEent",
    datetime(2023-10-10), "UpperCaseAccount", "User", "TestComputer2", "TestSource", "Security", "0000", "0", "4624", "4624 - An account was successfully logged on.", "Kerberos", "000", "000", "127.0.0.1", 1234, 0, "-", "-", "Kerberos", 10, "10 - RemoteInteractive", "-", "0x0", "-", "-", "-\\-", "-", "0x0", "-", "-", "UpperCaseAccount", "domain.net", "0x0", "0x0", "-", "-", "UpperCaseAccount", "-", "-", "-", "-", "-", "-", datetime(2023-10-10), "SecurityEent",
    datetime(2023-10-10), "UpperCaseAccount", "User", "TestComputer3", "TestSource", "Security", "0000", "0", "4624", "4624 - An account was successfully logged on.", "Kerberos", "000", "000", "127.0.0.1", 1234, 0, "-", "-", "Kerberos", 10, "10 - RemoteInteractive", "-", "0x0", "-", "-", "-\\-", "-", "0x0", "-", "-", "UpperCaseAccount", "domain.net", "0x0", "0x0", "-", "-", "UpperCaseAccount", "-", "-", "-", "-", "-", "-", datetime(2023-10-10), "SecurityEent",
    datetime(2023-10-10), "UpperCaseAccount", "User", "TestComputer1", "TestSource", "Security", "0000", "0", "4624", "4624 - An account was successfully logged on.", "Kerberos", "000", "000", "127.0.0.1", 1234, 0, "-", "-", "Kerberos", 10, "10 - RemoteInteractive", "-", "0x0", "-", "-", "-\\-", "-", "0x0", "-", "-", "UpperCaseAccount", "domain.net", "0x0", "0x0", "-", "-", "UpperCaseAccount", "-", "-", "-", "-", "-", "-", datetime(2023-10-10), "SecurityEent"
]);

Similarly, this can also be done with the "WindowsEvent" table, but since the issue is the same I am only showing you the effect for the "SecurityEvent" table.

To test the query mentioned by @HoGerl I needed to change some things:

let customTime = datetime(2023-10-10);
let endtime = customTime - 1d;
let starttime = customTime - 8d;
let threshold = 2.0;
(union isfuzzy=true
    (CustomEvent
    | where TimeGenerated >= (endtime)
    | where EventID == 4624 and LogonType == 10
    | summarize
        StartTimeUtc = min(TimeGenerated),
        EndTimeUtc = max(TimeGenerated),
        ComputerCountToday = dcount(Computer),
        ComputerSet = makeset(Computer),
        ProcessSet = makeset(ProcessName)
        by Account, IpAddress, AccountType, Activity, LogonTypeName),
    (WindowsEvent
    | where TimeGenerated >= (endtime)
    | where EventID == 4624
    | extend LogonType = tostring(EventData.LogonType)
    | where LogonType == 10
    | extend ProcessName = tostring(EventData.ProcessName)
    | extend Account = strcat(tostring(EventData.TargetDomainName), "\\", tostring(EventData.TargetUserName))
    | extend IpAddress = tostring(EventData.IpAddress)
    | extend TargetUserSid = tostring(EventData.TargetUserSid)
    | extend AccountType=case(Account endswith "$" or TargetUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(TargetUserSid), "", "User")
    | extend Activity="4624 - An account was successfully logged on."
    | extend LogonTypeName="10 - RemoteInteractive"
    | summarize
        StartTimeUtc = min(TimeGenerated),
        EndTimeUtc = max(TimeGenerated),
        ComputerCountToday = dcount(Computer),
        ComputerSet = makeset(Computer),
        ProcessSet = makeset(ProcessName)
        by Account, IpAddress, AccountType, Activity, LogonTypeName)
)
| join kind=inner (
    (union isfuzzy=true
        (CustomEvent
        | where TimeGenerated >= (starttime) and TimeGenerated < (endtime)
        | where EventID == 4624 and LogonType == 10
        | summarize ComputerCountPrev7Days = dcount(Computer) by Account = tolower(Account), IpAddress
        ),
        (WindowsEvent
        | where TimeGenerated >= (starttime) and TimeGenerated < (endtime)
        | where EventID == 4624 and EventData has ("10")
        | extend LogonType = toint(EventData.LogonType)
        | where LogonType == 10
        | extend Account = strcat(tostring(EventData.TargetDomainName), "\\", tostring(EventData.TargetUserName))
        | extend IpAddress = tostring(EventData.IpAddress)
        | summarize ComputerCountPrev7Days = dcount(Computer) by Account = tolower(Account), IpAddress)
    )
    )
    on Account, IpAddress
| extend Ratio = iff(isempty(ComputerCountPrev7Days), toreal(ComputerCountToday), ComputerCountToday / (ComputerCountPrev7Days * 1.0))
// Where the ratio of today to previous 7 days is more than double.
| where Ratio > threshold
| project
    StartTimeUtc,
    EndTimeUtc,
    Account,
    IpAddress,
    ComputerSet,
    ComputerCountToday,
    ComputerCountPrev7Days,
    Ratio,
    AccountType,
    Activity,
    LogonTypeName,
    ProcessSet
| extend
    timestamp = StartTimeUtc,
    AccountCustomEntity = Account,
    IPCustomEntity = IpAddress

Things i changed are:

• Added a let statement "customTime" to let the query run over the datetime specified in the custom table
• endtime and starttime are changed accordingly to the original query (where endtime means customTime ago(1d) and starttime is ago(8d).
• Removed the "ago()" statements from the query, since our let statements now are concrete dates and no relative times.

For TimeRange = Set in Query, we will get

If we change Lince 35 from the original query from by Account, ... to by Account = tolower(Account), ... we will get the desired result:

As I said, the same can be done with the "WindowsEvent" table with the same change in line 49.

To sum up, the changes i did are just so that you can test the modified query for yourself, the only changes that need to be done are changing lines 35 and 49 accordingly to "fix" the query.

[v-sudkharat]

Hi @kllnbrnjn, Thanks for sharing the information, we will check on this and get back to you by - 30 Nov. 2023

[kllnbrnjn]

Hey @v-sudkharat, any updates on this topic?

[v-muuppugund]

Hi @HoGerl / @kllnbrnjn ,
1.The main issue is we don't have data in our workspaces to replicate the same issue,
2.I have checked the Yaml Query shared @HoGerl its same in master

If needed we can have a call, please share your convenient time slots and email id,so will discuss in detail.

[v-muuppugund]

Hi @HoGerl / @kllnbrnjn ,

Gentle reminder ,
1.The main issue is we don't have data in our workspaces to replicate the same issue,
2.I have checked the Yaml Query shared @HoGerl its same in master

If needed we can have a call, please share your convenient time slots and email id,so will discuss in detail. or else share sample data to v-muuppugund@microsoft.com

[HoGerl]

hej hej,

for the first point, @kllnbrnjn showed up how to test it, i think.
regarding your second point: at my initial posting i mentioned the lines, which needs to be updated, to get the expected results.

[v-muuppugund]

hej hej,

for the first point, @kllnbrnjn showed up how to test it, i think. regarding your second point: at my initial posting i mentioned the lines, which needs to be updated, to get the expected results.

Hi @HoGerl ,Will cross check on it and get back to you.

[v-muuppugund]

Hi @HoGerl /@kllnbrnjn ,I am able to replicate the issue, didn't check above sample table, as don't have data, Will be doing the changes and raising a PR.

[v-muuppugund]

Hi @HoGerl / @kllnbrnjn ,I have done the changes and will be working on internal review for those changes,will update you once PR merged.