cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
How to use Azure Monitor Workbooks to map Sentinel data
By
CliveWatson
Published Nov 01 2019 09:18 AM 20.1K Views
CliveWatson
Microsoft
‎Nov 01 2019 09:18 AM

How to use Azure Monitor Workbooks to map Sentinel data

‎Nov 01 2019 09:18 AM

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:

clipboard_image_0.png 

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:

 

TimeGenerated TrafficDirection Country Longitude Latitude MaliciousIP
2019-10-30T22:47:29Z InboundOrUnknown United States -84.39 33.8 1.1.1.1


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.

Simply go to a online map like Bing Maps, click & copy on a area to get your own coordinates.
 

 

 

clipboard_image_1.png

 

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:

 

TimeGenerated Country DistanceKMandMiles Type TrafficDirection IndicatorThreatType DeviceVendor count_
2019-10-25T00:00:00Z People's Republic of China 8182.2 / 5084.2 WireData InboundOrUnknown Botnet   40

 

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)

clipboard_image_2.png



Now Press Edit

clipboard_image_3.png

Now do a "SAVE AS" and Name your new workbook, mine for example is called "MyNewMap" and complete any other requested fields.

clipboard_image_4.png

Optionally you can delete the other charts and tables in this workbook to tidy it up.

 

Now press, the second button called Edit

clipboard_image_5.png

Followed by Add Query 

clipboard_image_6.png

 

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.

 

 

clipboard_image_7.png

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 

Shortcut Tip: you can get the completed workbook ready query from here

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:

 
| where TimeGenerated > startofday(ago(daystoSearch)) and TimeGenerated < startofday(now())

with

| where TimeGenerated {TimeRange:query}

 

And also change this line.  

 
| summarize count() by bin(TimeGenerated,1d)
with
| summarize count() by bin(TimeGenerated,1h), Country, distance_in_miles, lon = Longitude, lat = Latitude
| top 10 by count_ 
 

 

clipboard_image_8.png

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

clipboard_image_9.png

Do you see data displayed?

 

Now for the final flourish -

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

 

clipboard_image_10.png

 

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

clipboard_image_12.png

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

clipboard_image_13.png

 

 

15 Comments
Version history
Last update:
‎Nov 02 2021 05:43 PM
Updated by:
Labels