Thursday, August 19, 2021

Azure AD Sign-in Logs + Workbooks = Know Who is Using Windows Hello for Business

Well, well, WELLLL!!  It’s been “a few” since I last posted, but here we are.

 

Today’s post is a joint effort between myself and two other people, each of whom is a lot smarter than I: Jeroen De Bonte and Michele Ferrari (pronounced like this).

 

As you likely know, we have some SUPERB developments in our Azure AD Authentication Methods Activity reporting in the Azure AD Portal. 

 

We have high-level registration “eye-candy” graphs such as:

  • “How many users are registered for MFA and what method(s) are they registered for?”
  • “How many people are registered for SSPR (Self-Service Password Reset)?”

image.png

We also have more granular registration information available:

image.png

  • TIP: You can edit the Columns for your viewing pleasure. 

image.png

  • If you select “Download,” the CSV file will have all the goods, regardless of the Column selections for the portal page/view.

image.png

 

Clicking over to the “Usage” tab and you’ll find helpful information there, too:

  • “How many people are using single-factor vs multi-factor auth?”
  • “How many people are using password sign-in, Windows Hello for Business, Passwordless, etc?”

image.png

 

image.png

 

A nagging question I had about Usage was “Who is using Windows Hello for Business?”

 

In the portal pages above, we are able to get a ‘count’ of Windows Hello for Business (WH4B) authentications, but we aren’t able to determine which specific people are using it. 

 

Enter Jeroen and Michele.

  • Jeroen is a "Customer Engineer" (formerly known as a PFE, or Premier Field Engineer) – he does a lot of WH4B workshops with our enterprise customers and has picked up more than a trick or two about it.
  • Michele is a former PFE, and now a Program Manager in our Identity division - and he knows Identity.

Michele published a nifty little Workbook to GitHub for the AAD Sign-in logs that creates a three-section WH4B ‘report’ – which Jeroen has been tweaking/sharing with customers during his Workshops. 

  • Section 1 - “Windows Hello for Business Usage – Auths per Device”
    • A pie chart showing each device and how many WH4B authentications to Azure AD have occurred from it
  • Section 2 - “Windows Hello for Business Usage – Per-Device and Per-User Authentication Counts”
    • A table showing each device, each user and the counts of times the user signed-in via WH4B
  • Section 3 - “Windows Hello for Business Usage – Global Locations of Authentications”
    • A map showing the general geography of the WH4B sign-ins

NOTE: The default time-frame is ‘last 30 days’ – I’ll show you how to change that in a bit

image.png

 

Cool, eh??!  I thought so, too!

 

So, how do we do this?

 

You likely already have the pre-reqs for this, especially if you’re doing much w/ Azure AD today.    

Go to the ‘Monitoring’ section in the AAD Portal and the ‘Workbooks’ blade there - select the ‘Empty’ Quick start template…

image.png

 

Select the “Advanced editor” button …

image.png

 

Copy/paste the JSON from the end of this post, then click Apply:

image.png

 

You’ll immediately see the results; select ‘Save as’ …

image.png

 

Give your Workbook a Title, select an Azure location and click ‘Save’

  • Subscription and Resource group values should be populated for you, based on your diagnostic settings in AAD:

image.png

MichaelHildebrand_12-1629310437335.png

 

Now, back on the Workbooks spot in the AAD Portal, hit Refresh a few times until your shiny new Workbook shows up. 

image.png

 

You can now run the Workbook at your leisure:

image.png

 

How do we change the time range?” 

  • Click Edit:

image.png

 

  • Click Edit (again) for the section you want to change…

image.png

 

  • Change the ‘Time Range’ to your liking (explore the other options, there, if you feel so inclined):

image.png

  • Your view is immediately refreshed:

image.png

 

  • If you want to cancel your changes, click ‘Cancel.’
  • If you want to save the changes, click ‘Done editing’ in the section you are editing:

image.png

 

  • Then click ‘Done Editing’ for the Workbook itself. 
  • If you want to save the changes to your Workbook, click Save (ironically, the save icon in the cloud portal is the pre-cloud era 3 ½” floppy disk).

image.png

 

That’s it – an easy and handy way to get WH4B sign-in info from Azure AD Sign-in Log data:

  • Who is using it
  • How frequently
  • From which devices
  • From where in the world

Cheers!

 

“Windows Hilde for Business”

 

P.S. If you want Michele’s code, it’s out on GitHub - GitHub - mistermik/WHfB_Workbook: Log Analytics Workbook to analyze the adoption of Windows Hello for Business

 

P.S.S.  Here’s the JSON code for my specific Workbook - based off of Michele’s GitHub work above, but tweaked by Jeroen and a bit further by me:

{

  "version": "Notebook/1.0",

  "items": [

    {

      "type": 3,

      "content": {

        "version": "KqlItem/1.0",

        "query": "SigninLogs\n| where Resource == \"Microsoft.aadiam\" and AppDisplayName == \"Windows Sign In\"\n| extend authenticationMethod_ = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)\n| extend succeeded_ = tostring(parse_json(AuthenticationDetails)[0].succeeded)\n| where authenticationMethod_== \"Windows Hello for Business\" and succeeded_ == \"true\"\n| extend authenticationStepDateTime_ = todatetime(tostring(parse_json(AuthenticationDetails)[0].authenticationStepDateTime))\n| extend displayName_ = tostring(DeviceDetail.displayName)\n| extend trustType_ = tostring(DeviceDetail.trustType)\n| extend deviceId_ = tostring(DeviceDetail.deviceId)\n| summarize Count=count(Identity) by displayName_, Identity\n| render piechart ",

        "size": 0,

        "showAnalytics": true,

        "title": "Windows Hello for Business Usage - Auths per Device",

        "timeContext": {

          "durationMs": 2592000000

        },

        "showExportToExcel": true,

        "exportToExcelOptions": "all",

        "queryType": 0,

        "resourceType": "microsoft.operationalinsights/workspaces",

        "chartSettings": {

          "yAxis": [

            "Count"

          ],

          "group": "displayName_",

          "createOtherGroup": null

        }

      },

      "name": "query - 2"

    },

    {

      "type": 3,

      "content": {

        "version": "KqlItem/1.0",

        "query": "SigninLogs\r\n| where Resource == \"Microsoft.aadiam\" and AppDisplayName == \"Windows Sign In\"\r\n| extend authenticationMethod_ = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)\r\n| extend succeeded_ = tostring(parse_json(AuthenticationDetails)[0].succeeded)\r\n| where authenticationMethod_== \"Windows Hello for Business\" and succeeded_ == \"true\"\r\n| extend authenticationStepDateTime_ = todatetime(tostring(parse_json(AuthenticationDetails)[0].authenticationStepDateTime))\r\n| extend displayName_ = tostring(DeviceDetail.displayName)\r\n| extend trustType_ = tostring(DeviceDetail.trustType)\r\n| extend deviceId_ = tostring(DeviceDetail.deviceId)\r\n| summarize Count = count() by displayName_, Identity",

        "size": 0,

        "title": "Windows Hello for Business Usage - Per-Device and Per-User Authentication Counts",

        "timeContext": {

          "durationMs": 2592000000

        },

        "queryType": 0,

        "resourceType": "microsoft.operationalinsights/workspaces",

        "visualization": "table"

      },

      "name": "query - 1"

    },

    {

      "type": 3,

      "content": {

        "version": "KqlItem/1.0",

        "query": "SigninLogs\r\n| where Resource == \"Microsoft.aadiam\" and AppDisplayName == \"Windows Sign In\"\r\n| extend authenticationMethod_ = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)\r\n| extend succeeded_ = tostring(parse_json(AuthenticationDetails)[0].succeeded)\r\n| where authenticationMethod_== \"Windows Hello for Business\" and succeeded_ == \"true\"\r\n| extend authenticationStepDateTime_ = todatetime(tostring(parse_json(AuthenticationDetails)[0].authenticationStepDateTime))\r\n| extend displayName_ = tostring(DeviceDetail.displayName)\r\n| extend trustType_ = tostring(DeviceDetail.trustType)\r\n| extend deviceId_ = tostring(DeviceDetail.deviceId)\r\n| extend latitude_ = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).latitude)\r\n| extend longitude = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).longitude)\r\n| extend city_ = tostring(LocationDetails.city)\r\n| summarize Count = count() by displayName_, latitude_, longitude\r\n",

        "size": 0,

        "title": "Windows Hello for Business Usage - Global Locations of Authentications",

        "timeContext": {

          "durationMs": 2592000000

        },

        "queryType": 0,

        "resourceType": "microsoft.operationalinsights/workspaces",

        "visualization": "map",

        "mapSettings": {

          "locInfo": "LatLong",

          "locInfoColumn": "city_",

          "latitude": "latitude_",

          "longitude": "longitude",

          "sizeAggregation": "Sum",

          "labelSettings": "displayName_",

          "legendMetric": "Count",

          "legendAggregation": "Sum",

          "itemColorSettings": {

            "nodeColorField": "Count",

            "colorAggregation": "Sum",

            "type": "heatmap",

            "heatmapPalette": "greenRed"

          }

        }

      },

      "name": "query - 2"

    }

  ],

  "fallbackResourceIds": [

    "/subscriptions/0db00944-ea10-4a46-8279-11a245ece0ba/resourcegroups/aad-loganalytics/providers/microsoft.operationalinsights/workspaces/aad-loganalytics-september-2020"

  ],

  "$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"

}

Posted at https://sl.advdat.com/3sugNhz