Checking wrong list in query

[bittib010]

Describe the bug
The Scheduled rule with GUID 957cb240-f45d-4491-9ba5-93430a3c08be stores the IP as ClientIPOnly, but looks at a nested list [0][0]. At our place, this is constantly wrong, and should only be:

Hopefully this will help others as well - I could not find any entries in our logs where this value actually works. We use this column in a title override, but keep getting blank on the IP.

[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 @bittib010 , Thanks for flagging this issue, we will investigate this issue and get back to you with some updates by 25Dec2023. Thanks!

[v-sudkharat]

Hi @bittib010, team is still checking on this issue and get back you by - 29-12-2023. Thanks!

[v-muuppugund]

Hi @bittib010 ,Could you please share more details on this issue,Which query?,which solution and what wrong list in query?

[bittib010]

Here is the query:

Azure-Sentinel/Solutions/Microsoft 365/Analytic Rules/RareOfficeOperations.yaml

Line 4 in e99fa2b

'Identifies Office operations that are typically rare and can provide capabilities useful to attackers.'

And looking at the query, like my original post here, showing the query and where it is looking into nested lists, in our dataset we never get a nested list, and therefore by looking at something that does not exist, we get an empty value:

Therefore we would like to see if changing it to (end of line 4):

  OfficeActivity
  | where Operation in~ ( "Add-MailboxPermission", "Add-MailboxFolderPermission", "Set-Mailbox", "New-ManagementRoleAssignment", "New-InboxRule", "Set-InboxRule", "Set-TransportRule")
  and not(UserId has_any ('NT AUTHORITY\\SYSTEM (Microsoft.Exchange.ServiceHost)', 'NT AUTHORITY\\SYSTEM (w3wp)', 'devilfish-applicationaccount') and Operation in~ ( "Add-MailboxPermission", "Set-Mailbox"))
  | extend ClientIPOnly = tostring(extract_all(@'\[?(::ffff:)?(?P<IPAddress>(\d+\.\d+\.\d+\.\d+)|[^\]]+)\]?', dynamic(["IPAddress"]), ClientIP)[0])
  | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])

[v-muuppugund]

Hi @bittib010 ,I am able to replicate the issue and have only 3 records in our work space,Please find below screen shot for reference

Could you please share sample data in CSV format to (v-muuppugund@microsoft.com),so will be modify the analytic rule and update you

[v-muuppugund]

Hi @bittib010 ,Gentle Reminder,,I am able to replicate the issue and have only 3 records in our work space,Please find below screen shot for reference

Could you please share sample data in CSV format to (v-muuppugund@microsoft.com),so will be modify the analytic rule and update you

[bittib010]

I'm sorry, im not able to do so in a while. I hoped you would get the same output as we did, but that shows only that there are differences in the data outputting. I'm currently unavailable to produce any dummy data for the next two weeks as I'm logged off main computer.

[v-muuppugund]

Hi @bittib010 , Working on the changes ,will be raising PR for the same.

[v-sudkharat]

Hi @bittib010, Team is still working on changes, and will raise a PR once it gets completed. Thanks!

[v-muuppugund]

Hi @bittib010 , Apologies for the delayed response, its a 1p connector, made the changes , need to push it as don't have permissions , Will update you.

[v-muuppugund]

Hi @bittib010 ,just want update,as its 1p connector,we are discussing internally process to push it and will update you once had an update

[v-muuppugund]

Hi @bittib010 ,we have defined the process internally for 1p connector and will be working on the PR,will update you once PR is pushed

[v-muuppugund]

Hi @bittib010 ,PR has merged,so we are closing the issue.