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:


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


 


 

Leave a Reply

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

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.