Automated email sending when running a KQL query

Question

Hello,

First of all, I'm quite new in Sentinel/KQL related stuff. I have this very basic KQL query to find sign-ins from countries not included in the "LocationDetails" argument. I'd like to automate this query and, if any results found, send an email to a recipient. I'm been looking for solutions to automate this process and found there are different approaches but non of them fits 100% with my expectations. My plan is to run the query twice a day and receive an email if the report generates any result. From the results I plan to narrow down the results by adding countries or excluding users from the query. Also, there's any way to store more than 30 days sign-ins?

Could you please give some advice? Thanks in advance.

SigninLogs

| where TimeGenerated > ago(30d)

| where not (LocationDetails.countryOrRegion in~ ( 'list of countries'))

| project TimeGenerated, UserPrincipalName, AppDisplayName, IPAddress, Status, LocationDetails.city, LocationDetails.countryOrRegion, DeviceDetail.operatingSystem

| order by TimeGenerated desc

Answer 1

Hello,

Microsoft Sentinel allows you to store data for 90days, you just need to increase the retention from 31 to 90. Fyi, 31days is the default set when the Log Analytics Workspace was created. source: https://learn.microsoft.com/en-us/azure/sentinel/configure-data-retention

I would run a Logic Apps (Playbook) which is scheduled to run twice per day, then run the KQL in that and then use it to send the email. The below is from an old post, but you can see the three steps. You will need to adjust the KQL, as you will only need to look back 12hrs rather than 30days I assume, or you will get duplicates when you have a match.

Create a 3 step Logic App:

 

1. Set your Recurrence (as required)
2. Run whatever KQL query
3. Send the email (or action you require), picking up the "attachment content" from step 2