Blog Post

Core Infrastructure and Security Blog
5 MIN READ

Failed Login Report Using Log Analytics and Logic Apps

BrandonWilson's avatar
BrandonWilson
Icon for Microsoft rankMicrosoft
Jul 09, 2019

My name is Brad Watts and I’m a SCOM PFE. I wanted to take a little bit of time to demonstrate how you can use Azure Log Analytics along with Azure Logic Apps to email out reports on important information. In this blog we will create a report of failed login attempts across all our monitored servers but this is just the tip of the ice berg of the useful information you can get from Log Analytics.

Before I show you how to build this solution, lets briefly talk about Log Analytics and Logic Apps.

 

Log Analytics

Log Analytics falls under the umbrella of Azure Monitor and provides a repository of data that is queries using the Kusto Query Language. Typically, data is inserted into Log Analytics using an agent that can be added directly in Azure, using your System Center Operations Manager environment, or manually installing the agent. You can configure a Log Analytics Workspace to collect event logs, performance data, log files, etc. You can also implement Monitoring Solutions such as the “Update Compliance” solution to collect additional information. For our example we are wanting to report on failed logins which come from the Security event log so we must have implemented Azure Security Center for this information to be available.

 

Going in depth on Security Center or Azure Monitor is beyond the scope of this blog but if you’re interested then happy reading!

 

https://docs.microsoft.com/en-us/azure/azure-monitor/

https://docs.microsoft.com/en-us/azure/security-center/

 

Logic Apps

Logic Apps provides a graphical interface to run a workflow that integrates different components together. There is an amazing number of products that Logic Apps integrates with. For our example we will first connect to a Log Analytics Workspace, run a Kusto Query, and then email the alerts using Office 365. We could have just as easily sent the results to Google Mail or a slack channel. If you’re interested in the connectors available in Logic Apps take a look at the following link:

https://docs.microsoft.com/en-us/azure/connectors/apis-list

 

If you’re interested in detail documentation on the product here you go!

https://docs.microsoft.com/en-us/azure/logic-apps/

 

Example Solution

I’m going to walk through creating a report that is sent out once a day. For this walkthrough we will use Log Analytics to pull a list of failed logins by computer, format it into a HTML file, and attach that result to an email. To accomplish this, we need to:

  1. Create the Kusto Query that can pull this information
  2. Design a Logic Apps job to schedule the query and then email the results out

 

Creating the Kusto Query

In this scenario we already have an Azure Log Analytics Workspace and Security Center enabled and reporting to our workspace with the proper agents deployed. To start with open the Log Analytics Workspace and open “Logs” to start your Kusto query.

 

 

 

First thing we need to do is pull from the correct data source. Whenever you are using Security Center (which included the Security Event Log) then we need to get information from SecurityEvent. So our query will start with

                SecurityEvent

 

Next we need to start filtering the data. You should always filter the data/time first so we will pull the last 24 hours using ago(1d). So our query will now look like this:

                SecurityEvent

                | where TimeGenerated >= ago(1d)

 

Next we will filter down to failed logins using Event Id 4625. Now our query looks like this:

                SecurityEvent

                | where TimeGenerated >= ago(1d)

                | where EventID == 4625

 

If you run this query you will get all the details on each failed login:

 

But our final result we want to summarize the number of failed logins for each unique Account Name and Computer combination. To do this we can add a summarize statement as follows:

                  SecurityEvent

                | where TimeGenerated >= ago(1d)

                | where EventID == 4625

                | summarize FailedLogins=count() by Account,Computer

 

This gives us the information we want but it would be nice to order by the number of failed logins. We can add a sort by FailedLogins desc at the end of our query. The final query should look like this:

                SecurityEvent

                | where TimeGenerated >= ago(1d)

                | where EventID == 4625

                | summarize FailedLogins=count() by Account,Computer

                | sort by FailedLogins desc

 

The results should be a table with the results we would like to email out.

 

Logic Apps

Now we need to move over to Logic Apps to build the logic that will email out the above results.

In Azure Logic Apps we’ll start by clicking “Add”

 

 

Give it a descriptive name like ‘Daily-Failed-Login-Report’ and hit create to get started. Start designing your solution by going to “Logic app designer”

 

 

Our solution is triggered off a schedule (once a day) so we can start with the template “Recurrence”

 

Change the recurrence to once per day and click on “+ New step”

 

Search for “Log Analytics” and choose “Run query and visualize results (preview)”

 

Click on “Sign in” to log into Azure and select the Azure Log Analytics Workspace you want to query. Note that you might want to use a service principle to connect instead of a standard AAD account.

 

Once you sign in you’ll need to provide the following information:

                Subscription: Azure Subscription where the Log Analytics Workspace is located

                Resource Group

                Workspace

                Query

                Chart Type

 

 

Once you have this filled out click on the “+ New Step” below your “Run query and visualize” activity. Search for “Office 365” and choose “Office 365 Outlook.” We can choose either “Send an email (V2) (preview)” or “Send an email from a shared mailbox (preview).” In this case because I don’t have a shared mailbox to use I’ll choose the first option.

 

Sign into your Office 365 account to get started. Fill out the information that you want for:

                To

                Subject

                Body

 

The last step is to add the HTML table from our Kusto query as an attachment. Drop down on the “Add new parameter” option and select “Attachments”

 

 

This will supply you with two new fields to fill out. The attachment name and attachment content. We want to use the data from the previous step. First click in the box for the “Attachment Name.” This will bring up a window on the right hand side where you can select dynamic data. In this case we only had one previous step which was to run the query. So under “Run query and visualize results” choose “Attachment Name”

 

 

Click in “Attachment Content” and this time choose “Attachment Name.”

That’s it! We need to first click on “Save” to commit our changes. After the save is complete we can click on the “Run” option to test our solution.

 

 

You should get an email soon after with an attachment that looks something like the following:

 

 

Summary

Azure Log Analytics is a powerful tool that allows you to gather a lot of data from all sorts of sources. The Kusto Query Language allows us to quickly access that data and determine trends and visualize the data. We walked through an easy way to utilize this power by scheduling a report using Azure Logic Apps.

 

Typically, any time you have a Kusto query that provides useful information you need to decide how to surface the data. The most common method to do this is:

  1. Create a view/dashboard using Log Analytics
  2. Create an alert that shows under the Unified Alerting experience in Azure Monitor
  3. Schedule the data to be delivered through Logic Apps. This could be through email like in this example or some other medium (SharePoint Document Library, Slack Channel, etc..)

 

I hope you enjoyed this walkthrough and see how you can utilize this in the future!

 

Brad Watts

Updated Jul 09, 2019
Version 1.0
  • Shweta , you could handle it in the Logic App. You could have a fork off of the "Run Query" activity that's on failure (something happened with the kusto query) and send a notification that something is wrong and you should check it out!

  • Shweta's avatar
    Shweta
    Copper Contributor

    Hi Brandon, thanks for this great writeup. How do you handle failed case? I can understand that in your scenario, you are pulling some data using Kusto query and then triggering an email. What if due to some reason query itself failed? In that case, email will not get trigger. Am I right? How to let user know or send another email saying that we are facing issue with Kusto.