This post is in two parts.
1. Workbook import instructions
2. The finished workbook
UPDATE - September 2020:
There have been some updates since 2019 to the map controls, so you may need to edit the Map Settings fields and sometimes the Latitude and Longitude entries.
or
I have now updated the Workbook, so please download the latest version from the Github. The Map Control should now work as expected, I also took the opportunity to update the workbook to the latest best practices and techniques. You'll now notices it uses Tabs to separate the data and has Help and Change control information. I also added a toggle for Kilometers vs. Miles.
The option "Add a manual entry" was the original behavior (now option #3) - this is now an ad-hoc entry option.
I think most uses will use Azure Active Directory (AAD - option #2) to get a dynamic list (if you have that data source);
or the new option #1 to have a list of their preferred locations, requires a workbook edit.
You can see the built-in help in this picture
So please download, look at the help file (just press the Help toggle, after you import the workbook).
I have created the workbook for you, so you just need to import it, using these instructions:
1. Workbook Import Instructions.
Create a NEW workbook
Enter into the Advanced Editor by pressing this button.
Note: Please delete the current info between the { and }.
You can find the related Workbook in the Sentinel GitHub, it’s called “UserMap.json”:
https://github.com/Azure/Azure-Sentinel/tree/master/Workbooks
Use the RAW button, then select all the text (CTRL-A), then copy (CTRL-C) and paste into the Advanced Editor pane.
The press Apply and remember to SAVE it (if you like it).
2. The finished Workbook
Note:
“Please check you local or country policies on personal data handling before using this solution,and ensure you gain any necessary consent”
You should now see the same World Map as per my last post. https://techcommunity.microsoft.com/t5/Azure-Sentinel/How-to-use-Azure-Monitor-Workbooks-to-map-Sent...
The KQL explained
This query relies on you having data in the Azure SigninLogs table. https://docs.microsoft.com/en-us/azure/sentinel/connect-azure-active-directory
Now we can also track people (assuming you have longitude and latitude info in the table). I have added an extra drop down dialog box so you can select your user from a list. We then use that users name to find where they logon .
Note: SigninLogs
location info is affected by your network, so you may see some inaccuracies depending on VPNs etc…
On the right I also mapped the locations again (as per the last blog post).
The Query used for the table on the left is (see query below, it is the example actually used in the workbook so wont work in Log Analytics ‘as is’ find a Log Analytics compatible version us this link (it wont run as our demo tenant doesn't have the required Table)
Go to Log Analytics and see the Query
Query Summary:
This query, grabs a few bits of data early on, like the Longitude and Latitude, City and State etc..
I then make use of the Prev() function to get the PREVious latitude and longitude (for the first location, that gets called “FirstLocation” as it doesn't have previous data.
We then use geo_distance_2points again to compare the users last location to the current and calculate that in miles (replace with KM if you prefer). I also remove any logons for the same location (this maybe useful to see – just comment out the line, starting with: where distance_in_miles !="0.0"
I finish by summarizing the data, and using a strcat to merge some columns (also taking the opportunity to add some Icons/Emojis).
KQL that works in my Azure Monitor Workbook
SigninLogs
//| where UserDisplayName == " "
| extend city_ = tostring(LocationDetails.city)
| extend state_ = tostring(LocationDetails.state)
| extend countryOrRegion_ = tostring(LocationDetails.countryOrRegion)
| extend latitude_ = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).latitude)
| extend longitude_ = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).longitude)
| order by TimeGenerated asc , city_ asc
| serialize
| extend pLat = prev(latitude_,1)
| extend pLon = prev(longitude_,1)
| extend distance_in_miles = iif(isnotempty(pLat),tostring(round(geo_distance_2points(todouble(longitude_), todouble(latitude_), todouble(pLon), todouble(pLat))/1609.344 ,2)),"FirstLocation")
| where distance_in_miles !="0.0"
| summarize count() by bin(TimeGenerated, 24h),
// UserDisplayName,
userNameLocation = strcat(UserDisplayName," " ,city_ , " ️ ",
countryOrRegion_),
visit_order = strcat(row_number(), ".",city_),
MilesTravelled=distance_in_miles
// latitude_,
// longitude_
| project-away count_
| order by TimeGenerated asc, visit_order asc
You should now be able to select a User then see their travel information / miles moved.
This is the same view (see below table) but run in Log Analytics. Please take a look the data (if you have it) and this is just one example of how you can use it.
