Skip to content

get failed logon attempts with Azure Log Analytics and PowerBI

In this blog post I will show how to query Azure Log Analytics for failed logon attempts on a VM in Azure.

For demonstrating purposes I have configured a VM with an open port 3389 to the internet.  This setup is not recommended in any situation. Diagnostics settings have been enabled on the VM and configured to send data to Azure Log Analytics.

In a well managed environment, Azure Security Center on a Standard Plan would warn immediately and recommend closing NSG ports or implementing security precautions like Just In Time VM Access.    Azure Security Center – Standard would also notify if detecting failed logon attempts etc.

 

First create a Log Analytics Workspace

hit “Analytics” to open the log loganalytics portal

type the following query in Azure Log Analytics:
SecurityEvent
| where EventID == 4625
| where TimeGenerated > ago(30d)
| project TimeGenerated , TargetUserName, IpAddress, Activity

 

the above screenshot lists a lot of failed logon attempt in the past 30 days   (10.000 lines)

//This Query Summarizes  by username (1.850 different usernames was used)

SecurityEvent
| where EventID == 4625
| where TimeGenerated > ago(30d)
| project TimeGenerated , TargetUserName, IpAddress, Activity
| summarize count() by TargetUserName

 

 

//This Query summarizes by IP address  (284 different IP addresses)

SecurityEvent
| where EventID == 4625
| where TimeGenerated > ago(30d)
| project TimeGenerated , TargetUserName, IpAddress, Activity
| summarize count() by IpAddress

Now i will add Log Analytics dataset to PowerBI

First download PowerBI desktop from Windows Store

 

then export Azure Log Analytics dataset to PowerBI (M Query)

 

copy the contect of the downloaded text file and open PowerBI – Get Data – Blank Query

 

select advanced editior and paste the content from the text file into the editor and press “done”

 

 

enter credentials with priviliges to Azure Log Analytics

 

select table and select data: timegenerated, ipaddress, activity and username

 

 

 

next add ArcSIS map for PowerBI and select data: ipaddress

ArcSIS will translate IP addresses to geo locations.

 

Thank you for reading.

 

 

Published inITMicrosoft Azure

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *