Home
%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%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%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%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154746i9A70E3A4857250D5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%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%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154747i2E71AA094E931384%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%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%20noopener%20noreferrer%20noopener%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%20style%3D%22width%3A%20624px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154748i9870FE2CCBECC710%2Fimage-dimensions%2F624x223%3Fv%3D1.0%22%20width%3D%22624%22%20height%3D%22223%22%20alt%3D%22clipboard_image_2.png%22%20title%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%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%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%20rel%3D%22noopener%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%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%20noopener%20noreferrer%20noopener%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%20style%3D%22width%3A%20680px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154749i3A6D7064DBB1FE6A%2Fimage-dimensions%2F680x439%3Fv%3D1.0%22%20width%3D%22680%22%20height%3D%22439%22%20alt%3D%22clipboard_image_3.png%22%20title%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%20style%3D%22width%3A%20675px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154751i7DA36062B0C0D3C7%2Fimage-dimensions%2F675x302%3Fv%3D1.0%22%20width%3D%22675%22%20height%3D%22302%22%20alt%3D%22clipboard_image_4.png%22%20title%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%20noopener%20noreferrer%20noopener%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%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%20noopener%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%20noopener%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%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%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%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%20style%3D%22width%3A%20681px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154750iD7791F3CB69EA114%2Fimage-dimensions%2F681x587%3Fv%3D1.0%22%20width%3D%22681%22%20height%3D%22587%22%20alt%3D%22clipboard_image_5.png%22%20title%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%20style%3D%22width%3A%20726px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154753i16D73AE61F14CC5A%2Fimage-dimensions%2F726x124%3Fv%3D1.0%22%20width%3D%22726%22%20height%3D%22124%22%20alt%3D%22clipboard_image_6.png%22%20title%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
Microsoft

This post is in two parts.

1. Workbook import instructions

2. The finished 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

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