365daysofkql hunt 1

1. How many distinct users signed into the tenant in February?

SigninLogs
| distinct UserPrincipalName
| count

841

2. Which application had the most signins? List the application name.

SigninLogs
| summarize count()by AppDisplayName
| sort by count_ desc 

CAttack

3. List three different users who signed in with any 'passwordless' method.

SigninLogs
| project TimeGenerated, AuthenticationDetails, UserPrincipalName
| extend AuthMethod = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)
| where AuthMethod in ("Passwordless phone sign-in","Mobile app notification","X.509 Certificate")
| distinct UserPrincipalName

Plenty to choose from

4. Which user signed into the most different applications? List their UserPrincipalName.

SigninLogs
| summarize AppList=dcount(AppDisplayName) by UserPrincipalName

pdemo@seccxpninja.onmicrosoft.com	

5. How many distinct guests signed into the tenant in February?

SigninLogs
| where UserType == "Guest"
| distinct UserPrincipalName 
| count

6. 770

SigninLogs
| where UserPrincipalName == "v-yopanc@microsoft.com"
| distinct Location

Hong Kong and India

7. Which application had the highest percentage of MFA signins? Need to calculate the percentage on this one, bit of a trick question

SigninLogs
| where ResultType == 0
| summarize
    TotalCount=count(),
    MFACount=countif(AuthenticationRequirement == "multiFactorAuthentication"),
    nonMFACount=countif(AuthenticationRequirement == "singleFactorAuthentication")
    by AppDisplayName
| project
    AppDisplayName,
    TotalCount,
    MFACount,
    nonMFACount,
    MFAPercentage=(todouble(MFACount) * 100 / todouble(TotalCount))
| sort by MFAPercentage desc 

Few options all at 100% - App Service, Power Automate AAD, BAG Solutions Installer, AzureADIPLogicApps, Windows Defender ATP for Flow

8. Which conditional access policy had the most successes in February?

SigninLogs
| project TimeGenerated, ConditionalAccessPolicies
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend CAPolicyName = tostring(ConditionalAccessPolicies.displayName)
| where CAResult == "success"
| summarize count()by CAPolicyName

MeganB MCAS Proxy

9. Which two users tried to access an application they didn't have access to?

SigninLogs
| where ResultType == "50105"
| distinct UserPrincipalName

chboeh@microsoft.com and prchugh@microsoft.com	

10. Which US state had the most sign ins and which had the least sign ins to the tenant in February?

SigninLogs
| extend State = tostring(LocationDetails.state)
| where Location == "US"
| summarize count()by State

Delaware the most, Utah the least