Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
How to use Azure Monitor Workbooks to map Sentinel data
Published Nov 01 2019 09:18 AM 19.5K Views
Microsoft

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
Copper Contributor

thanks for the blog post I've been looking how to do this exact thing for a few weeks now.

Microsoft

Hey Clive, we have updated the map functionality to now use multiple ways to get the location data such as using Azure Resource, Azure Location, or even using Country/Region name/code. Here is the full documentation with step by step examples.

https://github.com/microsoft/Application-Insights-Workbooks/blob/master/Documentation/Visualizations...

Copper Contributor

Hi @heyaworld,In my use case I'm plotting data for just North America and I only want to display North America region. Can you help me display only a specific region?

Microsoft

Hi, 

 

You could just amend the query (here I added a line to filter only on 'United States'):

 
| where Country == "United States" 
 

Or you could provide a list with:

 
| where Country in ("United States", "Canada"

 

The query currently looks for countries rather than Azure Regions or a geographic region 

 

clipboard_image_0.png

 

Personally I'd add a parameter called selectCountry to the Workbook (if you are using one), and query for countries or provide a list?

Copper Contributor

Hi @CliveWatson , Thanks for your reply. 

I already have data for just the US, but map is being displayed for the whole world which is taking extra space. I know I'm going to receive data for just US and hence only want to display map for US not world mapMap.PNG

I want something similar to this one, this one is Power BI visualization.

powerbi-map-us.png

Microsoft

Ah I see - sorry I misunderstood you.  AFAIK its a full world map - lets see if @heyaworld has any comments?

Copper Contributor

NP @CliveWatson, I should have added the screenshots already for message clarity. Yeah, let's wait for @heyaworld !

Microsoft

Hey @amirsharif ,

 

Currently we only allow Geo-Map(World Map) functionality, there is no way to hide other countries. Sorry for that.

Also, we can discuss more about perhaps having this functionality in future on our official teams channel, where we are very active.

 

Thank you,

Anurag

Copper Contributor

Thanks, Anurag.

 

That's great, I'll join the channel. 

@heyaworld.

Copper Contributor

Hey @CliveWatson, Do we have the privilege to change the height of the visualizations in Azure Sentinel Workbooks?

Microsoft

Hi @barcaraj 

You can control the grid width (see Style in the picture), column width (new - see "Column setting") and also the Size but not the height on its own, just the size overall. Annotation 2020-08-27 140934.jpg

Brass Contributor

Hey @CliveWatson..great post, I was searching for this a while. I have tried to follow all the steps mentioned above, but query is returning with errors. Really appreciate if you enlighten me, if i'm missing anything here.

 

AlphaBetaGamma_0-1601222440273.png

 

Microsoft

Do you have the required data in your Workspace, you need IP data in at least one of the these tables (highlighted in Red)?

You can use our demo data to test this: Go to Log Analytics and run query

 

Screenshot 2020-09-28 090643.jpg

Copper Contributor

Hello @CliveWatson,

 

Thank you for such a thorough walk-through on this topic. 

I am trying to follow this but I am getting the error "The name 'WireData' does not refer to any known table, tabular variable or function."

Overview of what I am trying to accomplish:

1.  Logs from WatchGuard Firebox Sent to Ubuntu Linux Syslog Server. ( Done )

2.  Syslog server forward logs to Sentinel. ( Done )

3. Create a workbook/Query to map data for Potential Malicious Events. ( Stuck )

 

I did read here https://docs.microsoft.com/en-us/azure/azure-monitor/insights/wire-data that WiredData is being deprecated, could this be why?

Microsoft

Hello @Panther_tux the workbook uses specific Tables, like Wiredata, so if your newly ingested data isn't in Wiredata or one of the other 5 your data wont show up, without making edits to the Workbook.   

 

If you have Syslog, your data is probably in the Syslog table, if its in CEF, then the workbook does look for that as I include the CommonSecurityLog table (which holds CEF). 

If you wish to map the data the data needs to have the Country or Longitude/Latitude information as well or a way to correlate to a table that does.

 

the columns where the Country, Longitude and Latitude for your data source maybe named differently, this works for CEF, but you would have to amend for your data / columns (assuming it has country / long and lat)? 

CommonSecurityLog
| extend TrafficDirection = iff(CommunicationDirection != "Outbound","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude, Confidence=ThreatDescription, Description=ThreatDescription
| where isnotempty(Country) or isnotempty(Longitude)
| summarize count() by Type, TrafficDirection, Longitude, Latitude, Country

 

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