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

[yashsedani]

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

[#2]

@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 

[#3]

@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

[#4]

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

[#5]

@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.

[#6]

@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_

[#7]

@CliveWatson 

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

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

[#8]

@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 

[#9]

@CliveWatson 

Like this?

I expanded all.

[#10]

@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

[#11]

@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?

[#12]

@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.

[#13]

@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

[#14]

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