Log Analytics Query for computer last login/active date and time

Contributor

Hi,

 

I am looking for a query where I can get last login/active date and time for computers in a separate column.

 

I am already using the below query for windows update

 

WaaSDeploymentStatus
| where UpdateCategory == "Quality" and TimeGenerated > ago(60d)
| summarize arg_max(ReleaseName, DeploymentStatus, DetailedStatus, DetailedStatusLevel, ExpectedInstallDate) by Computer
 
Please suggest what should we add to this query to get a new column which will give me last login/active date and time for computers
13 Replies

@yashsedani 

 

That table doesn't contain that data, so are you looking for a JOIN to a table that does, something like 

WaaSDeploymentStatus
| where UpdateCategory == "Quality" and TimeGenerated > ago(60d)
| summarize updateInfo = arg_max(ReleaseName, DeploymentStatus, DetailedStatus, DetailedStatusLevel, ExpectedInstallDate) by Computer
| join  (
SecurityEvent
| where EventID == 4624  // 4624 - An account was successfully logged on 
| summarize  LastHeatbeat = arg_max(TimeGenerated, *) by Computer
) on Computer
| project updateInfo , LastHeatbeat 

 

@CliveWatson 

I see the attached.

 

Syntax Error
'where' operator: Failed to resolve table or column expression named 'SecurityEvent' If issue persists, please open a support ticket. Request id: 4a962d40-99f6-4ed4-a66a-f8d9115c0c29

Hello @yashsedani

 

SecurityEvent is a table that could have that data, I used it as an example - that error suggests you don't have it, so you need to use another.   I wasn't sure if you thought the column of data was in the WaaSDeploymentStatus table or you needed from another table, if so do you know which one?

 

Thanks Clive

@CliveWatson 

 

I just need a query where i can get last login/active date and time of all the computers. 

The Query which is posted in my first message gives me the list if machines those are not up-to-date but few of them are not even in Active Directory (may be Object is deleted or renamed).

 

If adding a column to my query is not possible, i am comfortable with running another query for last login/active date and then will merge both the reports.

@yashsedani 

 

You haven't listed any Tables - and there are 100s - so its kind of hard for me to guess on what you have.

For instance I have 33 tables that contain a Computer column - only a few of those may have logon info.

union withsource = TableName *
| where isnotempty(Computer)
| summarize count() by TableName

 

This would list the last record per computer (assumes you have the Heartbeat table)

Heartbeat
| summarize  arg_max(TimeGenerated,*) by Computer

 

The reason I didn't suggest Heartbeat is that machines in the WaaS table don't always have the agent, so this doesn't work for me, but may for you?

 

WaaSDeploymentStatus
| where UpdateCategory == "Quality" and TimeGenerated > ago(60d)
| summarize updateInfo = arg_max(ReleaseName, DeploymentStatus, DetailedStatus, DetailedStatusLevel, ExpectedInstallDate) by Computer
| join  (
Heartbeat
| summarize  LastHeatbeat = arg_max(TimeGenerated, *) by Computer
) on Computer
| project updateInfo , LastHeatbeat 

 

For instance if you say you have SigninLogs then this may work

 

SigninLogs
| extend displayName_ = tostring(DeviceDetail.displayName) 
| summarize arg_max(TimeGenerated, *) by displayName_

 

 
 

 

 

 

 

@CliveWatson 

 

I am Sorry... Attached what table i see Group by Category/Solution.

 

I tried running the queries you post. Attached are the results.

 

 

@yashsedani 

 

We will get there I promise, none of those actually show the Table Name (just the Category and Solution names), its the next level of detail after those we need,  this query will show that detail:

union withsource = TableName *
| where isnotempty(Computer)
| summarize dcount(Computer) by TableName
| order by dcount_Computer desc

 

You should get a report like this (its this we need to see):

 

 

TableName dcount_Computer
InsightsMetrics 33
Perf 33
ConfigurationData 32
Operation 32
Heartbeat 32
Update 31
ProtectionStatus 30
SecurityBaseline 24
SecurityBaselineSummary 24
ConfigurationChange 21

 

With that I know two things: 

1. the real TableName (column 1) and

2. a count of unique computers,  which gives me a hint that there is some data to look at.

 

Thanks for your patience 

 

 

@CliveWatson 

Like this?

I expanded all.

@yashsedani 

 

Ah, that is Good and Bad, none of the Tables in that list that start with a "W" hold any logon / last accessed data (as far as I can see).  The only one with promise is the Usage table, but that only holds one computer!  I don't think you have collected any data in a table that pertains to login info?

 

LastScan will tell you that the computer was on at that time, so I added that.  I'm not sure what else I can do given the data.  Is this right?

 

WaaSDeploymentStatus
| where UpdateCategory == "Quality" and TimeGenerated > ago(60d)
| summarize arg_max(ReleaseName, DeploymentStatus, DetailedStatus, DetailedStatusLevel, ExpectedInstallDate, LastScan, UpdateReleasedDate ) by Computer

 

@CliveWatson 

 

Thanks! That is helpful I believe. Can we add one more column where it will give the Current windows update version of system like 1903 or 1909. Right now it is giving us the OS build version.

Also, can you help me on how to add all the tables or the important ones that are always helpful?

@CliveWatson 

Never mind!

 

I added OSVersion to query.

 The only thing I am curious about is how to get all the tables or atleast the useful once on my portal.

@yashsedani 

Did you see this link?

https://docs.microsoft.com/en-us/azure/azure-monitor/log-query/joins

 

SecurityEvent
| where EventID == 4624 // sign-in events
| project Computer, Account, TargetLogonId, LogonTime=TimeGenerated
| join kind= inner (
SecurityEvent
| where EventID == 4634 // sign-out events
| project TargetLogonId, LogoffTime=TimeGenerated
) on TargetLogonId
| extend Duration = LogoffTime-LogonTime
| project-away TargetLogonId1
| top 10 by Duration desc

Hello @James van den Berg 

 

The issue is that he doesn't (yet) have that data source.  @yashsedani  to on-board this data source, you will need to enable Azure Security Center - which needs the standard licence (31days eval is also available); so this would billable against you Azure enrollment (just like Log Analytics). 

You can also get SecurityEvent from Azure Sentinel - with the Security Events data collector, again its billable. 

 

https://azure.microsoft.com/en-us/pricing/details/security-center/ 

 

Thanks Clive