Report risky signins past 24 hours

Report risky signins past 24 hours

Identity theft is a major concern for most companies.

Complex Conditional Access rules/setups may unintentionally leave accounts without multifactor authenticaton.

Another aspect of this “not following up” on the technical security features that is set up, is that every day tasks tends to take precedence over tasks that has an aspect of “controlling/checking what is already set up”.

Most companies believe they have all their accounts covered with multifactor – while this may be the truth, there is a tendency to be so strong in that believe, that nothing is actually done to follow up on this.

Microsoft Sentinel can help you assist with this task, and to provide more accurate data regarding your user’s sign-ins. However, many companies do not have either the time or knowledge to take full advantage of a SIEM/SOAR solution.

To help, I have developed a Logic App that will check for risky sign-ins 24 hours backwards, based on the Azure Active Directory log “SigninLogs”

This is by no means a solution against identity theft but could work as a method to assist you to follow up on risky users and logins on a daily basis – simply by creating a report and email it to yourself.


  • Log Analytics Workspace
  • Basic KQL knowledge
  • Basic Logic App and API knowledge
  • Basic understanding of Access Control (IAM)
  • Basic understanding of Diagnostic Settings

Log Analytics Workspace & Diagnostic Settings

To start with, if you do not already have a Log Analytics Workspace, in which your SignInLogs ends up, create a Log Analytics Workspace.
You may name it whatever you like – if you are all new to this, I would recommend you to some planning. The Log Analytics Workspace you create, may end up being used for a future Sentinel solution.

I’ve named it “LOG-RiskyAADUsers”

Now, go to your Azure Active Directory -> Diagnostic Settings.
Click “Add diagnostic setting”

  1. Provide a name for the Diagnostic Setting (can be whatever you wish)
  2. Select SignInLogs (in general I would recommend many other log types also, but for this, SiginInLogs is sufficient)
  3. Select your subscription
  4. Select the Log Analytics Workspace in which you want data to end up in in
  5. Press Save

Sign-in logs from your Azure Active Directory will now start to show up in your Log Analytics Workspace.

The LogicApp

I have made an app registration in Azure Active Directory named “LogicApp”.
You may use an ordinary/standard Service Account (user) with username/password.
Make sure to provide it with a very long/complex password.
Also this user must be excluded from Conditional Access policies/requirements, or the Logic App will stop work after a while.

Now create a new Logic App

  1. Select your subscription
  2. Select the resource group in which the logic app will be stored
  3. Provide a Logic App name – be strict regarding naming your logic apps
  4. Select in which region the logic app will be stored
  5. Select Consumption
  6. You may set up Log Analytics for the logic app itself (monitoring/troubleshooting part)

Now go to your logic app. On first visit, you will be redirected to “Logic App Designer”
Select Recurrence

You will need to Add new parameter to get “Time zone” and “At these hours” visible

Press “New step”
Find “Run query and visualize results”

  1. Select your subscription
  2. Select the resource group in which the Log Analytics Workspace is in
  3. Select Log Analytics Workspace
  4. Select the Log Analytics Workspace in which the AAD log is sent to (first step of the guide)
  5. Insert this query

| where TimeGenerated > ago(1d) 
| where ConditionalAccessStatus == "notApplied" and RiskLevelDuringSignIn != "none"
//| where parse_json(AuthenticationDetails)[0].authenticationMethod != "Previously satisfied"
//| where parse_json(AuthenticationDetails)[0].authenticationStepRequirement != "Multi-factor authentication"
| project Identity, AuthenticationRequirement, ConditionalAccessPolicies, ConditionalAccessStatus, LocationDetails, RiskLevelDuringSignIn, SignInIdentifier, AuthenticationDetails, DeviceDetail, IPAddress
  1. Time Range: Set in query (you might have to type this yourself)
  2. Select Html Table
  3. Select the user/ with appropriate rights to the Workspace

You may include either one, or both, of the statements starting with “//” to get a more accurate query. Simply remove the “//” and the statements are included in the KQL.
Keep in mind the KQL is an example, that most likely need a little tweaking to get the output you need.

Add a New step
Find “Send an email (V2)”. You may find/select another option depending on your needs/requirements.

  1. Insert your email
  2. Write an appropriate subject
  3. Write an appropriate body and add “Attachment Content”
  4. Change connection to an identity with an email address

Press Save
Every day at 7 AM this Logic App will run and email you a report based on the KQL query inserted earlier in this guide.

The result

The output will look like this (slightly modified)

You can use this guide as a template, to build many other types of LogicApp/KQL based reports – have fun.

Table of Contents

Share this post
Search blog posts
Modern Workplace consultant and a Microsoft MVP in Enterprise Mobility.
Modern Workplace consultant and a Microsoft MVP in Windows and Devices for IT.

Infrastructure architect with focus on Modern Workplace and Microsoft 365 security.

Cloud & security specialist with focus on Microsoft backend products and cloud technologies.

Cloud & security specialist with focus on Microsoft 365.

Cloud & Security Specialist, with a passion for all things Cybersecurity

Cloud and infrastructure security specialist with background in networking.

Infrastructure architect with focus on design, implementation, migration and consolidation.

Infrastructure consultant with focus on cloud solutions in Office365 and Azure.

follow us in feedly

Follow on SoMe