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:
- Create the Kusto Query that can pull this information
- 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:
- Create a view/dashboard using Log Analytics
- Create an alert that shows under the Unified Alerting experience in Azure Monitor
- 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