IdentityInfo with analytics KQL query
Hi, I'm currently trying to create a KQL query for an alert rule in Sentinel. The log source upon which the alert rule is based, only contains the SAMAccountName, which prevents me from mapping it to an Account entity in the alert. I'm therefore trying to use the IdentityInfo table to lookup the AadUserId of the user, using the SAMAccountName. The issue I'm running into is that I want my query to run every 10 minutes, and look up data from the past 10 minutes, as this is most suitable given the nature of the alert and the log source. This however causes the lookup in the IdentityInfo table to also only check data from the last 10 minutes, which doesn't work as the data in that table may be much older and therefor fail the lookup of the AadUserId of the user. According to the documentation, the IdentityInfo table is refreshed every 14 days, so for it to work I'd have to create a query that checks all logging, including that of the log source, from the past 14 days, which is not what I want. Hopefully some of you have suggestions or ideas on how to make this work. Thanks a lot! Marek
Playbook when incident trigger is not working
Hi I want to create a playbook to automatically revoke session user when incident with specifics title or gravity is created. But after some test the playbook is'nt run autimacally, it work when I run it manually. I did'nt find what I do wrong. See the image and the code bellow. Thanks in advance! { "definition": { "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#", "contentVersion": "1.0.0.0", "triggers": { "Microsoft_Sentinel_incident": { "type": "ApiConnectionWebhook", "inputs": { "host": { "connection": { "name": "@parameters('$connections')['azuresentinel']['connectionId']" } }, "body": { "callback_url": "@{listCallbackUrl()}" }, "path": "/incident-creation" } } }, "actions": { "Get_incident": { "type": "ApiConnection", "inputs": { "host": { "connection": { "name": "@parameters('$connections')['azuresentinel-1']['connectionId']" } }, "method": "post", "body": { "incidentArmId": "@triggerBody()?['object']?['id']" }, "path": "/Incidents" }, "runAfter": {} }, "Send_e-mail_(V2)": { "type": "ApiConnection", "inputs": { "host": { "connection": { "name": "@parameters('$connections')['office365']['connectionId']" } }, "method": "post", "body": { "To": "email address removed for privacy reasons", "Subject": "Ceci est un test", "Body": "</p> <p class="\"editor-paragraph\"">@{body('Get_incident')?['id']}</p> <p class="\"editor-paragraph\"">@{body('Get_incident')?['properties']?['description']}</p> <p class="\"editor-paragraph\"">@{body('Get_incident')?['properties']?['incidentNumber']}</p> <p>", "Importance": "Normal" }, "path": "/v2/Mail" }, "runAfter": { "Get_incident": [ "Succeeded" ] } } }, "outputs": {}, "parameters": { "$connections": { "type": "Object", "defaultValue": {} } } }, "parameters": { "$connections": { "type": "Object", "value": { "azuresentinel": { "id": "/subscriptions/xxxx/providers/Microsoft.Web/locations/xxxxx/managedApis/xxxxxxx", "connectionId": "/subscriptions/xxxxxxx/resourceGroups/xxxxxx/providers/Microsoft.Web/connections/azuresentinel-Revoke-RiskySessions1", "connectionName": "azuresentinel-Revoke-RiskySessions1", "connectionProperties": { "authentication": { "type": "ManagedServiceIdentity" } } }, "azuresentinel-1": { "id": "/subscriptions/xxxxxx/providers/Microsoft.Web/locations/xxxx/managedApis/xxx", "connectionId": "/subscriptions/xxxxxxx/resourceGroups/xxxxx/providers/Microsoft.Web/connections/xxxx", "connectionName": "xxxxxx", "connectionProperties": { "authentication": { "type": "ManagedServiceIdentity" } } }, "office365": { "id": "/subscriptions/xxxxxx/providers/Microsoft.Web/locations/xxxxx/managedApis/office365", "connectionId": "/subscriptions/xxxxx/resourceGroups/xxxxxx/providers/Microsoft.Web/connections/o365-Test_Send-email-incident-to-xxxx", "connectionName": "o365-Test_Send-email-incident-to-xxxxx" } } } } }
Optimisation For Abnormal Deny Rate for Source IP
Hi, I have recently enabled the "Abnormal Deny Rate for Source IP" alert in Microsoft Sentinel and found it to be quite noisy, generating a large number of alerts many of which do not appear to be actionable. I understand that adjusting the learning period is one way to reduce this noise. However, I am wondering if there are any other optimisation strategies available that do not involve simply changing the learning window. Has anyone had success with tuning this rule using: Threshold-based suppression (e.g. minimum deny count)? Source IP allowlists? Frequency filters (e.g. repeated anomalies over multiple intervals)? Combining with other signal types before generating alerts? Open to any suggestions, experiences, or best practices that others may have found effective in reducing false positives while still maintaining visibility into meaningful anomalies. Thanks in advance,
Logic app - Escaped Characters and Formatting Problems in KQL Run query and list results V2 action
I’m building a Logic App to detect sign-ins from suspicious IP addresses. The logic includes: Retrieving IPs from incident entities in Microsoft Sentinel. Enriching each IP using an external API. Filtering malicious IPs based on their score and risk level. Storing those IPs in an array variable (MaliciousIPs). Creating a dynamic KQL query to check if any of the malicious IPs were used in sign-ins, using the in~ operator. Problem: When I use a Select and Join action to build the list of IPs (e.g., "ip1", "ip2"), the Logic App automatically escapes the quotes. As a result, the KQL query is built like this: IPAddress in~ ([{"body":"{\"\":\"\\\"X.X.X.X\\\"\"}"}]) Instead of the expected format: IPAddress in~ ("X.X.X.X", "another.ip") This causes a parsing error when the Run Query and List Results V2 action is executed against Log Analytics. ------------------------ Here's the For Each action loop who contain the following issue: Dynamic compose to formulate the KQL query in a concat, since it's containing the dynamic value above : concat('SigninLogs | where TimeGenerated > ago(3d) | where UserPrincipalName == \"',variables('CurrentUPN'),'\" | where IPAddress in~ (',outputs('Join_MaliciousIPs_KQL'),') | project TimeGenerated, IPAddress, DeviceDetail, AppDisplayName, Status') The Current UPN is working as expected, using the same format in a Initialize/Set variable above (Array/String(for IP's)). The rest of the loop : Note: Even if i have a "failed to retrieve" error on the picture don't bother with that, it's just about the dynamic value about the Subscription, I've entered it manually, it's working fine. What I’ve tried: Using concat('\"', item()?['ip'], '\"') inside Select (causes extra escaping). Removing quotes and relying on Logic App formatting (resulted in object wrapping). Flattening the array using a secondary Select to extract only values. Using Compose to debug outputs. Despite these attempts, the query string is always malformed due to extra escaping or nested JSON structure. I would like to know if someone has encountered or have the solution to this annoying problem ? Best regards
How to exclude IPs & accounts from Analytic Rule, with Watchlist?
We are trying to filter out some false positives from a Analytic rule called "Service accounts performing RemotePS". Using automation rules still gives a lot of false mail notifications we don't want so we would like to try using a watchlist with the serviceaccounts and IP combination we want to exclude. Anyone knows where and what syntax we would need to exlude the items on the specific Watchlist? Query: let InteractiveTypes = pack_array( // Declare Interactive logon type names 'Interactive', 'CachedInteractive', 'Unlock', 'RemoteInteractive', 'CachedRemoteInteractive', 'CachedUnlock' ); let WhitelistedCmdlets = pack_array( // List of whitelisted commands that don't provide a lot of value 'prompt', 'Out-Default', 'out-lineoutput', 'format-default', 'Set-StrictMode', 'TabExpansion2' ); let WhitelistedAccounts = pack_array('FakeWhitelistedAccount'); // List of accounts that are known to perform this activity in the environment and can be ignored DeviceLogonEvents // Get all logon events... | where AccountName !in~ (WhitelistedAccounts) // ...where it is not a whitelisted account... | where ActionType == "LogonSuccess" // ...and the logon was successful... | where AccountName !contains "$" // ...and not a machine logon. | where AccountName !has "winrm va_" // WinRM will have pseudo account names that match this if there is an explicit permission for an admin to run the cmdlet, so assume it is good. | extend IsInteractive=(LogonType in (InteractiveTypes)) // Determine if the logon is interactive (True=1,False=0)... | summarize HasInteractiveLogon=max(IsInteractive) // ...then bucket and get the maximum interactive value (0 or 1)... by AccountName // ... by the AccountNames | where HasInteractiveLogon == 0 // ...and filter out all accounts that had an interactive logon. // At this point, we have a list of accounts that we believe to be service accounts // Now we need to find RemotePS sessions that were spawned by those accounts // Note that we look at all powershell cmdlets executed to form a 29-day baseline to evaluate the data on today | join kind=rightsemi ( // Start by dropping the account name and only tracking the... DeviceEvents // ... | where ActionType == 'PowerShellCommand' // ...PowerShell commands seen... | where InitiatingProcessFileName =~ 'wsmprovhost.exe' // ...whose parent was wsmprovhost.exe (RemotePS Server)... | extend AccountName = InitiatingProcessAccountName // ...and add an AccountName field so the join is easier ) on AccountName // At this point, we have all of the commands that were ran by service accounts | extend Command = tostring(extractjson('$.Command', tostring(AdditionalFields))) // Extract the actual PowerShell command that was executed | where Command !in (WhitelistedCmdlets) // Remove any values that match the whitelisted cmdlets | summarize (Timestamp, ReportId)=arg_max(TimeGenerated, ReportId), // Then group all of the cmdlets and calculate the min/max times of execution... make_set(Command, 100000), count(), min(TimeGenerated) by // ...as well as creating a list of cmdlets ran and the count.. AccountName, AccountDomain, DeviceName, DeviceId // ...and have the commonality be the account, DeviceName and DeviceId // At this point, we have machine-account pairs along with the list of commands run as well as the first/last time the commands were ran | order by AccountName asc // Order the final list by AccountName just to make it easier to go through | extend HostName = iff(DeviceName has '.', substring(DeviceName, 0, indexof(DeviceName, '.')), DeviceName) | extend DnsDomain = iff(DeviceName has '.', substring(DeviceName, indexof(DeviceName, '.') + 1), "")Question about eDiscovery syntax
What would the appropriate eDiscovery syntax be if I wanted to perform a search on a single Exchange mailbox, capturing all email interactions between the mailbox's owner (i.e. email address removed for privacy reasons) and an external email address (i.e. email address removed for privacy reasons)?Researching a rule template "FailedLogonToAzurePortal"
Hello, I have the template rule "FailedLogonToAzurePortal"(https://github.com/Azure/Azure-Sentinel/blob/master/Detections/SigninLogs/FailedLogonToAzurePortal.yaml) and there is a column of data that I don't understand. The column is "FailedLogonCount" and it was showing inconclusive data because it was showing more data than it was... Here is an example: The issue states that 38 login failures have been detected, but if I investigate in the non-interactive login logs I only see one failure which matches the error code type "50173" but this only shows me one failure, I don't understand where the remaining 37 failures come from... Can you help me?, I am a beginner in KQL and I don't think I understand the context of the alert. Regards.
Scheduled Analytics Rule not triggering...
Hello, I am trying to trigger the following KQL query in a custom scheduled Analytics Rule... It is to identify ANY Global Administrator and verify if they have committed any activity (Sign-in) over the last 24 hours. Simple testing is to get a Global Administrator to sign in within the last 24 hours. Now the query triggers and returns records when run in the Logs pane... What I have noticed is that when activated in a custom-scheduled Analytics Rule, it fails to return records! Now the time range set for the analytics rule (query frequency & lookback duration) aligns properly with the query logic or any log ingestion delay. Query scheduling Run query every: 1 day Lookup data from the last: 1 day The funny thing is, when testing the KQL query in the Analytics Rule and Set rule logic/View query results, if the FIRST ATTEMPT returns no results (in the simulation), after repeatedly testing (clicking the test link), it DOES return records! Why is there a time-lag? How can I ensure the query triggers correctly, returning records accordingly, and related Incidents? This is the KQL query... let PrivilgedRoles = dynamic(["Global Administrator"]); let PrivilegedIdentities = IdentityInfo | summarize arg_max(TimeGenerated, *) by AccountObjectId | mv-expand AssignedRoles | where AssignedRoles in~ (PrivilgedRoles) | extend lc_AccountUPN = tolower(AccountUPN) | summarize AssignedRoles=make_set(AssignedRoles) by AccountObjectId, AccountSID, lc_AccountUPN, AccountDisplayName, JobTitle, Department; SigninLogs | where TimeGenerated > ago (1d) | extend lc_UserPrincipalName = tolower(UserPrincipalName) | join kind=inner PrivilegedIdentities on $left.lc_UserPrincipalName == $right.lc_AccountUPN | project TimeGenerated, AccountDisplayName, AccountObjectId, lc_AccountUPN, lc_UserPrincipalName, AppDisplayName, ResultType, ResultDescription, IPAddress, LocationDetails
