Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

Report to determine PC Availability

Created: 21 Jan 2013 • Updated: 29 Jan 2013 | 11 comments
This issue has been solved. See solution.

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

Comments 11 CommentsJump to latest comment

SK's picture

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.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

m.taylor's picture

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.

chrismcevoy72's picture

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

Andrew Bosch's picture

Maybe something like this will help?

 

SELECT ci.Name
      ,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
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

------------------------------------
Sr. Principal SQA Engineer
Symantec

SOLUTION
chrismcevoy72's picture

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! :)

chrismcevoy72's picture

Sorry the above was accidentally sent from my old account blush

chrismcevoy72's picture

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

Andrew Bosch's picture

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

------------------------------------
Sr. Principal SQA Engineer
Symantec

Sally5432's picture

@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?  

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

Andrew Bosch's picture

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.

------------------------------------
Sr. Principal SQA Engineer
Symantec

chrismcevoy72's picture

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