United Kingdom Endpoint Management User Group

We have a large number of PCs that are available for students to use that do not require booking and are free to use use providing they can find one that is not being used.  We want to try and publish which of these PCs is currently available and thought I'd ask here if anyone has done anything similar using the Altiris DB.  We need to be able to determine the current power state of a PC (on or off), and if the power state is on then if there is a user currently logged on to it or not.

Does the db have anything hidden within it's depths that could help us?

We're using CMS 7.1 SP1

The Evt_AeX_Client_LogOn table could be used to list those machines that have a logon event time record bit no logoff record (the logoff record will also contain the login time, which will allow you to marry up the two records).

The Inv_OS_Operating_System.[Last Boot Up Time] column can be used to tell you when a computer was started on a particluar day.  Usefull if you know all machines will be swicthed off everyday.

In addition to SK's recommendation I suggest the Inv_Client_Task_Resources table where HasTaskAgent and IsActive are True. I've found this to be a reliable way to determine if a machine is online. Though I use it more as a client count per task server report to ensure task servers are operational.

Edit: Just did a quick test and maybe it's really only useful for how I'm using it, not to confirm specific machines are available.

Thanks for the info - the user logon/logoff info looks like something we can work with.  We just need something to confirm if a PC is currently turned on.  Hard to believe there's nothing in Altiris that records this info??

Maybe something like this will help?

Hi Andrew - that seems to work a treat and is definitely something we can work with.  It shows PCs currently in standby as off but we can live with that.  Many thanks! :)

Sorry the above was accidentally sent from my old account 

One final question related to this - how would I add the PC's Altiris Organisational Group Name to the report?

@Andrew - Thanks for your help, this is pretty helpful.  How often would the data in that report be updated? Is it based on the last inventory sent to the NS?  

The logon/logoff information is updated when the Altiris Agent detects one of those events on the PC.  So, it is almost "realtime".  The biggest bottleneck will be the NS itself processing the NSE's and then updating the SQL tables.

As for the IsActive, this is a "heartbeat", if you will, from the Client Task Agent (Task Server).  I believe they "check in" every 10 minutes or so, by default.  So, fairly accurate and up-to-date.

This should get you what you need.  A caveat is that if a machine is part of multiple Organizational Groups, then it will list all of them.  We can always filter out specific ones if you want.

SELECT ci.Name
              ,i.Name AS [Organizational Group]
              ,CASE WHEN ctr.IsActive = 1 THEN 'Yes'
                          WHEN ctr.IsActive = 0 THEN 'No' END AS PoweredOn
              ,CASE WHEN lastTime.Event = 'Logoff' THEN 'No'
                          WHEN lastTime.Event = 'Logon' THEN 'Yes'
                          ELSE 'N/A' END AS UserLoggedOn

FROM vRM_Computer_Item ci
JOIN Inv_Client_Task_Resources ctr
   ON ctr._ResourceGuid = ci.Guid
JOIN ScopeMembership sm
   ON sm.ResourceGuid = ci.Guid
JOIN Item i
   ON i.Guid = sm.ScopeCollectionGuid
LEFT JOIN (SELECT _ResourceGuid, _eventTime, clo.[Event]
                    FROM Evt_AeX_Client_LogOn clo
                    JOIN (SELECT MAX (_id) AS ID
                               FROM Evt_AeX_Client_LogOn
                               GROUP BY _ResourceGuid) maxid
                       ON maxid.ID = clo._id) lastTime
   ON lastTime._ResourceGuid = ctr._ResourceGuid

@Andrew - thanks for the second solution - I managed to strip out the duplicates and it's working a treat :)