0

Let me say I have NO idea how to use Kusto, with that being said here we go! I went to my AAD workbook and got a query to pull up logins. I then added some info to get a list of users who have signed into my services. How do I get it to pull users who have NOT signed in, in the last 30 days?

let data = SigninLogs
    | union ADFSSignInLogs;
data
| where TokenIssuerType in ('AzureAD', 'ADFederationServices') or '*' in ('AzureAD', 'ADFederationServices')
| project TimeGenerated,UserPrincipalName, Status_dynamic, UserDisplayName, Identity, AppDisplayName, AppId, ResourceDisplayName
| extend errorCode = Status_dynamic.errorCode
| extend SigninStatus = case(errorCode == 0, "Success", errorCode == 50058, "Pending user action", errorCode == 50140, "Pending user action", errorCode == 51006, "Pending user action", errorCode == 50059, "Pending user action", errorCode == 65001, "Pending user action", errorCode == 52004, "Pending user action", errorCode == 50055, "Pending user action", errorCode == 50144, "Pending user action", errorCode == 50072, "Pending user action", errorCode == 50074, "Pending user action", errorCode == 16000, "Pending user action", errorCode == 16001, "Pending user action", errorCode == 16003, "Pending user action", errorCode == 50127, "Pending user action", errorCode == 50125, "Pending user action", errorCode == 50129, "Pending user action", errorCode == 50143, "Pending user action", errorCode == 81010, "Pending user action", errorCode == 81014, "Pending user action", errorCode == 81012, "Pending user action", "Failure")
| where SigninStatus == '*' or '*' == '*' or '*' == 'All Sign-ins'
| make-series SignIns = count() default = 0
    on TimeGenerated
    from ago(30d) to now() step 1d
    by SigninStatus,UserDisplayName,UserPrincipalName,AppDisplayName,ResourceDisplayName

How do I get my query to just show users who have NOT logged into my services in the last 30 days?

Airizzo
  • 165
  • 1
  • 12
  • Hi @Airizzo, if the answer below helped, please accept (see [this](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work?answertab=active#tab-top) to know why and how). If you're still missing some info, please post a comment saying what you're missing, and I'll help. Thanks. – Slavik N Sep 11 '21 at 13:18

1 Answers1

0

You'll need to use arg_max to get the latest login timestamp for every username, and then you can filter those for which the last login time was more than 30 days (30d) ago.

...
| summarize arg_max(LoginTime, *) by Username
| where LoginTime < ago(30d)

Obviously, you'll need to adjust the column names to match your schema (actual column names).

Slavik N
  • 4,705
  • 17
  • 23
  • How would I plug that into the code? Or would be by itself like ```let data = SigninLogs | union ADFSSignInLogs; data | summarize arg_max(TimeGenerated, *), min(TimeGenerated) by UserDisplayName, | where TimeGenerated < ago(30d)``` – Airizzo Sep 02 '21 at 19:04
  • It shouldn't be by itself. I assume you already have a table/query that has the username and login-time in every record, and from this you want to extract the records with last login per user, but only for users who logged in more than 30 days ago. So you need to append the two KQL lines I wrote to the end of your existing query, to get these results. – Slavik N Sep 03 '21 at 05:19