How to use Azure Monitor Workbooks to map Sentinel data

For this post I'm going to start with the query on the Sentinel Home Page that shows Potential MaliciousIP events.  Here is the example from my portal:

Note: There are indicators for Inbound and Outbound MaliciousIP on this map.  I often get asked how we get this info, please read on.

Prepare your query 

This is the basic Kusto Query Language (KQL) I have used to look at the Log Analytics data.  I have changed the format slightly to left align it, and removed the detected regions for now - i.e. kept it simple.  You can use any data source with Longitude or Latitude data.

union isfuzzy=true 
(W3CIISLog
| extend TrafficDirection = "InboundOrUnknown", Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude), 
(DnsEvents
| extend TrafficDirection = "InboundOrUnknown", Country= RemoteIPCountry, Latitude = RemoteIPLatitude, Longitude = RemoteIPLongitude),
(WireData
| extend TrafficDirection = iff(Direction != "Outbound","InboundOrUnknown", "Outbound"), Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude), 
(WindowsFirewall
| extend TrafficDirection = iff(CommunicationDirection != "SEND","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude), 
(CommonSecurityLog
| extend TrafficDirection = iff(CommunicationDirection != "Outbound","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude, Confidence=ThreatDescription, Description=ThreatDescription), 
(VMConnection
| where Type == "VMConnection"
| extend TrafficDirection = iff(Direction != "outbound","InboundOrUnknown", "Outbound"), Country=RemoteCountry, Latitude=RemoteLatitude, Longitude=RemoteLongitude)

Summary of the query:

The query looks in 1-6 Tables and if there are Inbound or Outbound MaliciousIP addresses it notes them, the data feed provides lots of good columns of data, in this article its the Longitude and Latitude I'm mainly focusing on. 

Now we have the basics, we can add some extra filtering, in the below I've added some Let statements at the beginning and some Time filtering at the end - to get 7 days of data in this example case.

let daystoSearch = 7d; // Please enter how many days worth of data to look at?
let myLongitude  = -0.925915;  // Microsoft Campus UK
let myLatitude   = 51.461377;
union isfuzzy=true   
(W3CIISLog
| extend TrafficDirection = "InboundOrUnknown", Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude), 
(DnsEvents
| extend TrafficDirection = "InboundOrUnknown", Country= RemoteIPCountry, Latitude = RemoteIPLatitude, Longitude = RemoteIPLongitude),
(WireData
| extend TrafficDirection = iff(Direction != "Outbound","InboundOrUnknown", "Outbound"), Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude),     
(WindowsFirewall
| extend TrafficDirection = iff(CommunicationDirection != "SEND","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude),     
(CommonSecurityLog
| extend TrafficDirection = iff(CommunicationDirection != "Outbound","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude, Confidence=ThreatDescription, Description=ThreatDescription),    
(VMConnection
    | where Type == "VMConnection"
    | extend TrafficDirection = iff(Direction != "outbound","InboundOrUnknown", "Outbound"), Country=RemoteCountry, Latitude=RemoteLatitude, Longitude=RemoteLongitude)
// start of main logic 
| where TimeGenerated > startofday(ago(daystoSearch)) and TimeGenerated < startofday(now())   
| where isnotempty(MaliciousIP) and isnotempty(Country) and isnotempty(Latitude) and isnotempty(Longitude)

Here is a simplified view of those returned results:

Now I want to enrich this data, in this case not only do I want to know which Country the inbound attack is happening from, I also want to know the distance from me and the attacker.  You could use this same technique for Impossible Travel scenarios as well, if you have Longitude and Latitude information.

Introduction to a geospatial query

KQL has some great geospatial functions (4 at the time of writing); in this example I'm using this one https://docs.microsoft.com/en-us/azure/kusto/query/geo-distance-2points-function 

The query returns the Longitude/Latitude and all I need to add is a default location for this function to use, I used the Microsoft Campus in the UK. I will then use this to measure the distance between two points.

I then have these lines of KQL to add.  Lines #1 and #2 here are used to get the distances (in both KM and Miles) - you may only need one of these?  I then summarize the returned data to get a much smaller set of rows (which is efficient).

| extend distance_in_kilometers = geo_distance_2points(Longitude, Latitude, myLongitude, myLatitude)/1000.00 
| extend distance_in_miles = geo_distance_2points(Longitude, Latitude, myLongitude, myLatitude)/1609.344 
| summarize count() by bin(TimeGenerated,1d),
Country, 
DistanceKMandMiles = strcat(round(distance_in_kilometers,1)," / ",round(distance_in_miles,1) ),
Type,
TrafficDirection,
IndicatorThreatType,
DeviceVendor
| sort by TimeGenerated asc

Returned result of our now optimized and enriched query - this Country is ~8182 KMs away:

The finished query (almost):

//
// Look back at data and see if any from six named tables have Malicious IP info 
// This query is based on the Sentinel home page "Potential malicious events" view 
// We'll also enrich the data with KiloMeter distance of each entry to our home location 
//
// Please enter how many days worth of data to look at?
let daystoSearch = 7d; 
// Microsoft Campus UK - adjust as required with your own Location Longitude / Latitude 
let myLongitude= -0.925915; 
let myLatitude = 51.461377;
// isFuzzy will allow us to continue on errors or if a table is empty etc...
union isfuzzy=true 
(W3CIISLog
| extend TrafficDirection = "InboundOrUnknown", Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude), 
(DnsEvents
| extend TrafficDirection = "InboundOrUnknown", Country= RemoteIPCountry, Latitude = RemoteIPLatitude, Longitude = RemoteIPLongitude),
(WireData
| extend TrafficDirection = iff(Direction != "Outbound","InboundOrUnknown", "Outbound"), Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude), 
(WindowsFirewall
| extend TrafficDirection = iff(CommunicationDirection != "SEND","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude), 
(CommonSecurityLog
| extend TrafficDirection = iff(CommunicationDirection != "Outbound","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude, Confidence=ThreatDescription, Description=ThreatDescription), 
(VMConnection
| where Type == "VMConnection"
| extend TrafficDirection = iff(Direction != "outbound","InboundOrUnknown", "Outbound"), Country=RemoteCountry, Latitude=RemoteLatitude, Longitude=RemoteLongitude)
// Start of main logic 
| where TimeGenerated > startofday(ago(daystoSearch)) and TimeGenerated < startofday(now()) 
| where isnotempty(MaliciousIP) and isnotempty(Country) and isnotempty(Latitude) and isnotempty(Longitude)
| extend distance_in_kilometers = geo_distance_2points(Longitude, Latitude, myLongitude, myLatitude)/1000.00 
| extend distance_in_miles = geo_distance_2points(Longitude, Latitude, myLongitude, myLatitude)/1609.344 
| summarize count() by bin(TimeGenerated,1d),
Country, 
DistanceKMandMiles = strcat(round(distance_in_kilometers,1)," / ",round(distance_in_miles,1) ),
Type,
TrafficDirection,
IndicatorThreatType,
DeviceVendor
| sort by TimeGenerated asc

Make your own World Map in Azure Monitor Workbooks

Now let take that information and make our own Map from it, we will use Azure Monitor workbooks for that. Now rather than using an 'Empty Workbook', I'm starting with the Workspace Usage one (as its has a drop down for Subscription, Workspace and Time already configured)

Now Press Edit

Now press, the second button called Edit

Followed by Add Query 

Now paste in the completed KQL query, then select the Workspace drop down and select the "workspace" check box. 

Now you can swap between workspaces (and Subscriptions) using the drop down - especially handy if you are suing Azure Lighthouse or have multiple subscriptions/workspaces.

We can also use the TimeRange picker now, but before you do we need to adjust the KQL again

Adjust the KQL to make it Workbook friendly 

If you want to make the changes yourself and ignore the 'tip':

1. remove or comment out the let command as shown in the next screen shot.  

2. Set TimeRange to the TimeRange check box - this will tell the KQL to get that info from the drop down.  Also shown in the next screen shot.

3. We need a 3rd and 4th change (to get the TimeRange Picker to work) 

Replace the line:

And also change this line.  

Now when you click on TimeRange (the top one); you can now select the Time Range required.

Do you see data displayed?

Now for the final flourish -

Creating our Map, by swapping the Visualization to "Map"

I then used these settings, when you are happy with mine or use yours, press Apply, then Save & Close

You should now have a Heatmap with the Distance in Miles (or KM) displayed.  Now just press Done Editing and Save your workbook!