Forum Discussion

yashsedani's avatar
yashsedani
Brass Contributor
Feb 12, 2020

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
  • 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

    • CliveWatson's avatar
      CliveWatson
      Icon for Microsoft rankMicrosoft

      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 

  • 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 

     

    • yashsedani's avatar
      yashsedani
      Brass Contributor

      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
      • CliveWatson's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft

        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

Resources