Feb 12 2020
05:49 AM
- last edited on
Apr 08 2022
10:17 AM
by
TechCommunityAP
Feb 12 2020
05:49 AM
- last edited on
Apr 08 2022
10:17 AM
by
TechCommunityAP
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
Feb 12 2020 11:29 AM
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
Feb 12 2020 12:56 PM
I see the attached.
Feb 12 2020 01:35 PM
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
Feb 12 2020 03:42 PM
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.
Feb 13 2020 08:39 AM
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_
Feb 13 2020 10:58 AM
I am Sorry... Attached what table i see Group by Category/Solution.
I tried running the queries you post. Attached are the results.
Feb 13 2020 12:05 PM
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
Feb 13 2020 12:30 PM
Feb 13 2020 12:48 PM
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
Feb 13 2020 03:34 PM
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?
Feb 13 2020 03:44 PM
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.
Feb 16 2020 10:18 PM
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
Feb 17 2020 02:18 AM
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