Query to get the last active time of a resource(disk/ vm) within a Azure subscription

Question

I have been trying to get the last active time of a disk/VM using Kusto Query Language on Azure portal. Is it possible to track it? I want this information to alert the user if that resource is not used for more than 14 days.

Also, could anyone help me finding out if there is any other way to query the list of unused resources (say for N days) within a subscription?

Thanks!

Answer 1

As far as I know, you need to find the max authenticationStepDateTime to be more than 14 days ago.

To get the list of unused resources (say for N=15 days) within a subscription using Kusto Query Language on Azure portal, try using the sample query like below:

let SigninUsersWithin15Days = SigninLogs
| extend d = parse_json(AuthenticationDetails)
| extend LoginTimestamp = todatetime(d[0].authenticationStepDateTime)
| where AppDisplayName == "Azure Portal" and OperationName == "Sign-in activity" and isnotempty(AlternateSignInName)
| summarize max(LoginTimestamp) by AlternateSignInName, Identity
| where max_LoginTimestamp < ago(15d)
| distinct AlternateSignInName;

• The query starts with a reference SigninLogs.
• The data is then piped through extend clause that creates a new column by computing a value in every row.
• The pipe is used to bind together data transformation operators. Both the where clause and pipe (|) delimiter are key to writing KQL queries.
• To this extend, give authentication details json file as input.
• You can use extend to provide an alias for timestamps that compute the session duration based on queried date and time.
• This is piped to where clause which filters Sign-in activity of all resources in Azure Portal by given columns.
• Here we included AlternateSignIn name which displays login details of users.
• Finally, the query displays list of unused resources less than 15 days.

For your reference, please find these links:Ref1, Ref2