Forum Discussion
yashsedani
Feb 12, 2020Brass Contributor
Log Analytics Query for computer last login/active date and time
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
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- CliveWatson
Microsoft
Hello JamesvandenBerg
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
- CliveWatson
Microsoft
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
- yashsedaniBrass Contributor
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- CliveWatson
Microsoft
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