Looking for logged on table

[Carl Fortin]

Hi,

I'm looking for the database table that contains the logged-in account information.
I want to create a filter with all computers where no one is logged in.

Thanks!

[Greg Zielinski]

I'm curious how they do it.  I wonder if they made a stored procedure. Personally, I query this event table.

Evt_AeX_Client_LogOn 

https://knowledge.broadcom.com/external/article/181824/how-to-report-which-user-last-logged-ont.html

[Greg Zielinski]

This table can have these events.

Startup
Shutdown
Logon
Logoff.

So when I edit the SQL for the most recent event where it isn't 'Logon'.  I believe you get what you are looking for.

-- This is the main query used to select the columns that interest us
select tm.[name] as 'Computer name', evt.Domain, evt.[User], tm.[Last event time], evt.[event]
  from Evt_AeX_Client_LogOn evt
  join (
-- This is the sub query that returns the computer guid, computer name, last event time, and event.
select lg._ResourceGuid, vc.name, max(lg._eventTime) as 'Last event time'
  from Evt_AeX_Client_LogOn lg
  join vComputer vc
    on lg._ResourceGuid = vc.Guid
 where vc.name like '%'
 group by lg._ResourceGuid, vc.Name, lg.[event]
) as tm
    on evt._eventTime = tm.[Last event time]
   and evt._ResourceGuid = tm._ResourceGuid
 where evt.[event] != 'Logon'
 order by [Last event time] desc

[Carl Fortin]

Thanks!, I got it working with the query you provided. 

I also had to exclude machines that were not connected in the last hour to get a better result.

[Shawn Mayhew]

 Slight tweak - 

 
select   ac.name, max(evt._eventTime) as 'Last event time', evt.[event], 'No' as 'User logon since Startup?'
  from Evt_AeX_Client_LogOn evt WITH (NOLOCK)
  left join dbo.Inv_AeX_AC_Identification ac WITH (NOLOCK) on ac._ResourceGuid = evt._ResourceGuid
 
 
 where evt.[event] = 'Startup'  and (ac.[Client Date] > getdate ()-30 and ac.[Client Date] <getdate ()-.1)
 
  and evt._ResourceGuid not in 
  (select lg._ResourceGuid 
  from Evt_AeX_Client_LogOn lg WITH (NOLOCK)
   join dbo.Inv_AeX_AC_Identification ac WITH (NOLOCK) on lg._ResourceGuid = ac._ResourceGuid 
    
 where lg.Event = 'Logon'
 group by lg._ResourceGuid, ac.Name, lg.[event])
 
 
 Group by ac.name ,   evt.[event]  

------------------------------
Altiris Admin since 2002
------------------------------

[Greg Zielinski]

Wouldn't this section effectively filter out all GUIDs that have ever had an event that equals 'Logon'? Resulting in a "machines that have never had 'Logon' in the history of the Evt_AeX_Client_LogOn table" vs "Where the MOST RECENT event was 'Logon'"?

  and evt._ResourceGuid not in 
  (select lg._ResourceGuid 
  from Evt_AeX_Client_LogOn lg WITH (NOLOCK)
   join dbo.Inv_AeX_AC_Identification ac WITH (NOLOCK) on lg._ResourceGuid = ac._ResourceGuid 
    
 where lg.Event = 'Logon'
 group by lg._ResourceGuid, ac.Name, lg.[event])

[Shawn Mayhew]

maybe - added in the max event and had no change in my numbers. I think that is because we are doing a date range on the startup  

• I was able to use this query to find over 700 machines that have started but no user logged on. then spot checked a bunch of them. Machines were up and running with no windows log on.  

 

select   ac.name, max(evt._eventTime) as 'Last event time', evt.[event], 'No' as 'User logon since Startup?'

  from Evt_AeX_Client_LogOn evt WITH (NOLOCK)

  left join dbo.Inv_AeX_AC_Identification ac WITH (NOLOCK) on ac._ResourceGuid = evt._ResourceGuid

 

 

 where evt.[event] = 'Startup' 

and (ac.[Client Date] > getdate ()-30 and ac.[Client Date] <getdate ()-.1)

 

 and evt._ResourceGuid not in 

  (select lg._ResourceGuid

from Evt_AeX_Client_LogOn lg WITH (NOLOCK)

join dbo.Inv_AeX_AC_Identification ac WITH (NOLOCK) on lg._ResourceGuid = ac._ResourceGuid 

    where lg.Event = 'Logon'

group by lg._ResourceGuid, ac.Name, lg.[event])

 

 

 Group by ac.name ,   evt.[event] 

------------------------------
Altiris Admin since 2002
------------------------------