How to use Azure Sentinel to follow a Users travel and map their location

Published Nov 04 2019 02:07 PM 9,674 Views
Microsoft

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).

UserMapHelp.jpg

 

 

 

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

clipboard_image_0.png

 

Enter into the Advanced Editor by pressing this button.

clipboard_image_1.png

 

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

 

clipboard_image_2.png

 

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

 

clipboard_image_3.png

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).

clipboard_image_4.png

 

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.

 

clipboard_image_5.png

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.

 

clipboard_image_6.png

 

8 Comments
Visitor

Hi @Clive Watson,

 

I'd send a issue to Github for this workbook, https://github.com/Azure/Azure-Sentinel/issues/747

 

Thanks!

Occasional Contributor

Hello @Clive Watson ...It seems the dropdown limits to 1000 values currently (For example, UserData tab- SelectUser dropdown). Is there any way to increase the limit by chance?

Microsoft

Hi,

 

I was planning to add an extra filter, it looks like this example:

 

userMapv1-3demo.gif

 

It wont increase the 1000 limit but:

1. You can see how many users you have retuned.  And I display an error if >= 1000 are seen! 

2. You can switch to a mode, "Select User by Letter", where you can search by initial letter (and see the count of those) - I appreciate its possible to have 1000 people with the same first letter of their name, but it gives at lot more room

 

 

What do you think?


@AlphaBetaGamma PM me if you would like to test a copy?

Microsoft

...and now with free text search, as an option:

 userMapv1-3ademo.gif

Occasional Contributor

This is really cool. This free text search option is exactly what we have in mind and needed. Thank you @Clive Watson 

Microsoft

Azure-Sentinel/UserMap.json at master · Azure/Azure-Sentinel (github.com)  has just bee approved into the Azure Sentinel Github 

Contributor

Is there a way to convert userdisplayname from it's service principal value to the human name?

I've seen some incidents in Sentinel where the human name is shown, but the analytic kql doesn't present any logic that the userdisplayname has been converted to human form?

Microsoft

If you run a search on SignnLogs, there are three columns that have name, are you asking for Identity or UserPrincipalName to also be used, can you provide an example?

 

SigninLogs
| project UserDisplayName, Identity, UserPrincipalName


Results example
UserDisplayName | Identity | UserPrincipalName
Clive W, Clive W, Clive@anemailname.com

%3CLINGO-SUB%20id%3D%22lingo-sub-1458881%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Azure%20Sentinel%20to%20follow%20a%20Users%20travel%20and%20map%20their%20location%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1458881%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20send%20a%20issue%20to%20Github%20for%20this%20workbook%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fissues%2F747%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fissues%2F747%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-981716%22%20slang%3D%22en-US%22%3EHow%20to%20use%20Azure%20Sentinel%20to%20follow%20a%20Users%20travel%20and%20map%20their%20location%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-981716%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20post%20is%20in%20two%20parts.%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E1.%20Workbook%20import%20instructions%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E2.%20The%20finished%20workbook%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EUPDATE%20-%20September%202020%3A%3C%2FFONT%3E%26nbsp%3B%3CBR%20%2F%3EThere%20have%20been%20some%20updates%20since%202019%20to%20the%20map%20controls%2C%20so%20you%20may%20need%20to%20edit%20the%20Map%20Settings%20fields%20and%20sometimes%20the%20Latitude%20and%20Longitude%20entries.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20now%20updated%20the%20Workbook%2C%20so%20please%20download%20the%20latest%20version%20from%20the%20Github.%26nbsp%3B%20The%20Map%20Control%20should%20now%20work%20as%20expected%2C%20I%20also%20took%20the%20opportunity%20to%20update%20the%20workbook%20to%20the%20latest%20best%20practices%20and%20techniques.%26nbsp%3B%20%26nbsp%3BYou'll%20now%20notices%20it%20uses%20Tabs%20to%20separate%20the%20data%20and%20has%20Help%20and%20Change%20control%20information.%26nbsp%3B%20I%20also%20added%20a%20toggle%20for%20Kilometers%20vs.%20Miles.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20option%20%22Add%20a%20manual%20entry%22%20was%20the%20original%20behavior%20(now%20option%20%233)%20-%20this%20is%20now%20an%20ad-hoc%20entry%20option.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20think%20most%20uses%20will%20use%20Azure%20Active%20Directory%20(AAD%20-%20option%20%232)%20to%20get%20a%20dynamic%20list%20(if%20you%20have%20that%20data%20source)%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20the%20new%20option%20%231%20to%20have%20a%20list%20of%20their%20preferred%20locations%2C%20requires%20a%20workbook%20edit.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EYou%20can%20see%20the%20built-in%20help%20in%20this%20picture%3CBR%20%2F%3ESo%20please%20download%2C%20look%20at%20the%20help%20file%20(just%20press%20the%20Help%20toggle%2C%20after%20you%20import%20the%20workbook).%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22UserMapHelp.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215789i911AF82A2C44D7AF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22UserMapHelp.jpg%22%20alt%3D%22UserMapHelp.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20created%20the%20workbook%20for%20you%2C%20so%20you%20just%20need%20to%20import%20it%2C%20using%20these%20instructions%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%20id%3D%22toc-hId-1852410017%22%3E1.%20Workbook%20Import%20Instructions.%26nbsp%3B%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%20a%20NEW%20workbook%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22clipboard_image_0.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154746i9A70E3A4857250D5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22clipboard_image_0.png%22%20alt%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20into%20the%20%3CSTRONG%3EAdvanced%20Editor%3C%2FSTRONG%3E%20by%20pressing%20this%20button.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22clipboard_image_1.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154747i2E71AA094E931384%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22clipboard_image_1.png%22%20alt%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3ENote%3A%20Please%20delete%20the%20current%20info%20between%20the%20%7B%26nbsp%3B%20and%20%26nbsp%3B%7D.%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20find%20the%20related%20Workbook%20in%20the%20Sentinel%20GitHub%2C%20it%E2%80%99s%20called%20%E2%80%9C%3CSTRONG%3EUserMap.json%3C%2FSTRONG%3E%E2%80%9D%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Ftree%2Fmaster%2FWorkbooks%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Ftree%2Fmaster%2FWorkbooks%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22clipboard_image_2.png%22%20style%3D%22width%3A%20624px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154748i9870FE2CCBECC710%2Fimage-dimensions%2F624x223%3Fv%3D1.0%22%20width%3D%22624%22%20height%3D%22223%22%20title%3D%22clipboard_image_2.png%22%20alt%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20the%20RAW%20button%2C%20then%20select%20all%20the%20text%20(CTRL-A)%2C%20then%20copy%20(CTRL-C)%20and%20paste%20into%20the%20%3CSTRONG%3EAdvanced%20Editor%3C%2FSTRONG%3E%20pane.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20press%20%3CSTRONG%3EApply%3C%2FSTRONG%3E%20and%20remember%20to%20%3CSTRONG%3ESAVE%3C%2FSTRONG%3E%20it%20(if%20you%20like%20it).%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%20id%3D%22toc-hId--699746944%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%20id%3D%22toc-hId-1043063391%22%3E2.%20The%20finished%20Workbook%3C%2FH2%3E%0A%3CP%3ENote%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E%E2%80%9CPlease%20check%20you%20local%20or%20country%20policies%20on%20personal%20data%20handling%20before%20using%20this%20solution%2Cand%20ensure%20you%20gain%20any%20necessary%20consent%E2%80%9D%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EYou%20should%20now%20see%20the%20same%26nbsp%3BWorld%20Map%20as%20per%20my%20last%20post.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Sentinel%2FHow-to-use-Azure-Monitor-Workbooks-to-map-Sentinel-data%2Fba-p%2F971818%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Sentinel%2FHow-to-use-Azure-Monitor-Workbooks-to-map-Sentinel-data%2Fba-p%2F971818%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%20id%3D%22toc-hId--1705607075%22%3EThe%20KQL%20explained%3C%2FH3%3E%0A%3CP%3EThis%20query%20relies%20on%20you%20having%20data%20in%20the%20Azure%20SigninLogs%20table.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Fconnect-azure-active-directory%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Fconnect-azure-active-directory%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22clipboard_image_3.png%22%20style%3D%22width%3A%20680px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154749i3A6D7064DBB1FE6A%2Fimage-dimensions%2F680x439%3Fv%3D1.0%22%20width%3D%22680%22%20height%3D%22439%22%20title%3D%22clipboard_image_3.png%22%20alt%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENow%20we%20can%20also%20track%20people%20(assuming%20you%20have%20longitude%20and%20latitude%20info%20in%20the%20table).%26nbsp%3B%20I%20have%20added%20an%20extra%20drop%20down%20dialog%20box%20so%20you%20can%20select%20%3CEM%3Eyour%3C%2FEM%3E%20user%20from%20a%20list.%20We%20then%20use%20that%20users%20name%20to%20find%20where%20they%20logon%20.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3ENote%3A%20SigninLogs%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3Elocation%20info%20is%20affected%20by%20your%20network%2C%20so%20you%20may%20see%20some%20inaccuracies%20depending%20on%20VPNs%20etc%E2%80%A6%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3EOn%20the%20right%20I%20also%20mapped%20the%20locations%20again%20(as%20per%20the%20last%20blog%20post).%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22clipboard_image_4.png%22%20style%3D%22width%3A%20675px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154751i7DA36062B0C0D3C7%2Fimage-dimensions%2F675x302%3Fv%3D1.0%22%20width%3D%22675%22%20height%3D%22302%22%20title%3D%22clipboard_image_4.png%22%20alt%3D%22clipboard_image_4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Query%20used%20for%20the%20table%20on%20the%20left%20is%20(see%20query%20below%2C%20it%20is%20the%20example%20actually%20used%20in%20the%20workbook%20so%20wont%20work%20in%20Log%20Analytics%20%E2%80%98as%20is%E2%80%99%20find%20a%20Log%20Analytics%20compatible%20version%20us%20this%20link%20(it%20wont%20run%20as%20our%20demo%20tenant%20doesn't%20have%20the%20required%20Table)%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA6VUwY7TMBC9V%25252Bo%25252FDLlsLJm2WyokDj2xgksBiV3OkZsM2VkldmQ7LYHlGxAnjlz4CMRPwScwbrbZpktzwYcosd%25252B8efNmnEvKNemVyd14NJ3ewvYaLcI7h%25252FaCXFWo5rUqEZZLOPt0iQWmPhx9PhuPbgE%25252FeNQZpOSbBGAJ3jhvSefxyqTKk9EX6BUVbhIQAg5CnFcek6GQHaIXk5pae9u8sW8xZ%25252BBg9BG2x1Mw0tcZ9iVXyjpMbpzR8UnWHM1zY2xGmrU5ISZ7qj6%25252F0fldgv%25252Fm31O1CfgILawbuKISX6JGy7gMlEtB3nWB3wOSW0SqoI94KKxaKc%25252BSKoubuDNBnosexOgO0tXRx2TEvdEpJqSTkgp0HED0Pianjcey8k0cMgnZ1Wm5G1nM1SVd7LwypL1jLzJTrws8yCYk3O%25252FuZR5uBpH9b84mpudPZ88mTxYLkHMhZPSCrPN7h6NdAe1oP9T%25252FaBnNJrNoZ1xdlsoG43YjFItg95p03LNcwnxxLeR4BP9Y0yk%25252Fju7PCSivmoEBsVfKZrJr%25252FB4fU0Tw5%25252FvXHxCBbFstIex8%25252B%25252FX75xeITic4vjanZPPakCOftFPWybBmm%25252Bi6XKON2fRoErXpB2heBVOvrNpgUWC2fOD3gG33YzkE6kYldKyy5ob%25252FSo%25252FVVjVtscnwXZG9OncXZjz6CzKqc2UGBQAA%2Ftimespan%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20see%20the%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%20id%3D%22toc-hId--159310245%22%3EQuery%20Summary%3A%3C%2FH4%3E%0A%3CP%3EThis%20query%2C%20grabs%20a%20few%20bits%20of%20data%20early%20on%2C%20like%20the%20Longitude%20and%20Latitude%2C%20City%20and%20State%20etc..%3C%2FP%3E%0A%3CP%3EI%20then%20make%20use%20of%20the%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fprevfunction%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3E%20Prev()%3C%2FA%3E%20function%20to%20get%20the%20%3CSTRONG%3EPREV%3C%2FSTRONG%3Eious%20latitude%20and%20longitude%20(for%20the%20first%20location%2C%20that%20gets%20called%20%E2%80%9CFirstLocation%E2%80%9D%20as%20it%20doesn't%20have%20previous%20data.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20then%20use%20%3CSTRONG%3Egeo_distance_2points%3C%2FSTRONG%3E%20again%20to%20compare%20the%20users%20last%20location%20to%20the%20current%20and%20calculate%20that%20in%20miles%20(replace%20with%20KM%20if%20you%20prefer).%26nbsp%3B%20I%20also%20remove%20any%20logons%20for%20the%20same%20location%20(this%20maybe%20useful%20to%20see%20%E2%80%93%20just%20comment%20out%20the%20line%2C%20starting%20with%3A%20%3CEM%3Ewhere%26nbsp%3Bdistance_in_miles%26nbsp%3B!%3D%220.0%22%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3EI%20finish%20by%20summarizing%20the%20data%2C%20and%20using%20a%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fstrcatfunction%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Estrcat%3C%2FA%3E%20to%20merge%20some%20columns%20(also%20taking%20the%20opportunity%20to%20add%20some%20Icons%2FEmojis).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%20id%3D%22toc-hId-1583500090%22%3EKQL%20that%20works%20in%20my%20Azure%20Monitor%20Workbook%26nbsp%3B%3C%2FH4%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESigninLogs%0A%2F%2F%7C%20where%20UserDisplayName%20%3D%3D%20%22%20%20%22%0A%7C%20extend%20city_%20%20%3D%20tostring(LocationDetails.city)%20%0A%7C%20extend%20state_%20%3D%20tostring(LocationDetails.state)%20%0A%7C%20extend%20countryOrRegion_%20%3D%20tostring(LocationDetails.countryOrRegion)%20%0A%7C%20extend%20latitude_%20%20%3D%20tostring(parse_json(tostring(LocationDetails.geoCoordinates)).latitude)%20%0A%7C%20extend%20longitude_%20%3D%20tostring(parse_json(tostring(LocationDetails.geoCoordinates)).longitude)%20%0A%7C%20order%20by%20TimeGenerated%20asc%20%2C%20city_%20asc%0A%7C%20serialize%20%0A%7C%20extend%20pLat%20%3D%20prev(latitude_%2C1)%0A%7C%20extend%20pLon%20%3D%20prev(longitude_%2C1)%0A%7C%20extend%20distance_in_miles%20%3D%20iif(isnotempty(pLat)%2Ctostring(round(geo_distance_2points(todouble(longitude_)%2C%20todouble(latitude_)%2C%20todouble(pLon)%2C%20todouble(pLat))%2F1609.344%20%2C2))%2C%22FirstLocation%22)%0A%7C%20where%20distance_in_miles%20!%3D%220.0%22%0A%7C%20summarize%20count()%20by%20bin(TimeGenerated%2C%2024h)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20%20%20UserDisplayName%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20userNameLocation%20%3D%20strcat(UserDisplayName%2C%22%20%20%22%20%2Ccity_%20%2C%20%22%20%EF%B8%8F%20%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20countryOrRegion_)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20visit_order%20%3D%20strcat(row_number()%2C%20%22.%22%2Ccity_)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MilesTravelled%3Ddistance_in_miles%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20%20%20latitude_%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20%20%20longitude_%0A%7C%20project-away%20count_%0A%7C%20order%20by%20TimeGenerated%20asc%2C%20visit_order%20asc%0A%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20should%20now%20be%20able%20to%20select%20a%20User%20then%20see%20their%20travel%20information%20%2F%20miles%20moved.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22clipboard_image_5.png%22%20style%3D%22width%3A%20681px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154750iD7791F3CB69EA114%2Fimage-dimensions%2F681x587%3Fv%3D1.0%22%20width%3D%22681%22%20height%3D%22587%22%20title%3D%22clipboard_image_5.png%22%20alt%3D%22clipboard_image_5.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThis%20is%20the%20same%20view%20(see%20below%20table)%20but%20run%20in%20Log%20Analytics.%26nbsp%3B%20Please%20take%20a%20look%20the%20data%20(if%20you%20have%20it)%20and%20this%20is%20just%20one%20example%20of%20how%20you%20can%20use%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22clipboard_image_6.png%22%20style%3D%22width%3A%20726px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154753i16D73AE61F14CC5A%2Fimage-dimensions%2F726x124%3Fv%3D1.0%22%20width%3D%22726%22%20height%3D%22124%22%20title%3D%22clipboard_image_6.png%22%20alt%3D%22clipboard_image_6.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-981716%22%20slang%3D%22en-US%22%3E%3CP%3EBuilding%20on%20my%20last%20post%2C%20now%20we%20will%20map%20a%20users%20location%20and%20see%20how%20far%20they%20have%20travelled%20between%20them.%26nbsp%3B%20I'll%20also%20provide%20the%20completed%20project%20as%20a%20file%20for%20you%20to%20import.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-981716%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Sentinel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1865222%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Azure%20Sentinel%20to%20follow%20a%20Users%20travel%20and%20map%20their%20location%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865222%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B...It%20seems%20the%20dropdown%20limits%20to%201000%20values%20currently%20(For%20example%2C%20UserData%20tab-%20SelectUser%20dropdown).%20Is%20there%20any%20way%20to%20increase%20the%20limit%20by%20chance%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867208%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Azure%20Sentinel%20to%20follow%20a%20Users%20travel%20and%20map%20their%20location%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867208%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20was%20planning%20to%20add%20an%20extra%20filter%2C%20it%20looks%20like%20this%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22userMapv1-3demo.gif%22%20style%3D%22width%3A%20789px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232334iC913CC79C88009C5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22userMapv1-3demo.gif%22%20alt%3D%22userMapv1-3demo.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20wont%20increase%20the%201000%20limit%20but%3A%3C%2FP%3E%0A%3CP%3E1.%20You%20can%20see%20how%20many%20users%20you%20have%20retuned.%26nbsp%3B%20And%20I%20display%20an%20error%20if%20%26gt%3B%3D%201000%20are%20seen!%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.%20You%20can%20switch%20to%20a%20mode%2C%20%22Select%20User%20by%20Letter%22%2C%20where%20you%20can%20search%20by%20initial%20letter%20(and%20see%20the%20count%20of%20those)%20-%20I%20appreciate%20its%20possible%20to%20have%201000%20people%20with%20the%20same%20first%20letter%20of%20their%20name%2C%20but%20it%20gives%20at%20lot%20more%20room%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20do%20you%20think%3F%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F810782%22%20target%3D%22_blank%22%3E%40AlphaBetaGamma%3C%2FA%3E%26nbsp%3BPM%20me%20if%20you%20would%20like%20to%20test%20a%20copy%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867322%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Azure%20Sentinel%20to%20follow%20a%20Users%20travel%20and%20map%20their%20location%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867322%22%20slang%3D%22en-US%22%3E%3CP%3E...and%20now%20with%20free%20text%20search%2C%20as%20an%20option%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22userMapv1-3ademo.gif%22%20style%3D%22width%3A%20622px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232339i0BE14E60C539E52C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22userMapv1-3ademo.gif%22%20alt%3D%22userMapv1-3ademo.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867450%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Azure%20Sentinel%20to%20follow%20a%20Users%20travel%20and%20map%20their%20location%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867450%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20really%20cool.%20This%20free%20text%20search%20option%20is%20exactly%20what%20we%20have%20in%20mind%20and%20needed.%20Thank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1931629%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Azure%20Sentinel%20to%20follow%20a%20Users%20travel%20and%20map%20their%20location%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1931629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fblob%2Fmaster%2FWorkbooks%2FUserMap.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure-Sentinel%2FUserMap.json%20at%20master%20%C2%B7%20Azure%2FAzure-Sentinel%20(github.com)%3C%2FA%3E%26nbsp%3B%20has%20just%20bee%20approved%20into%20the%20Azure%20Sentinel%20Github%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Sep 01 2020 02:07 AM
Updated by: