Deployment and Imaging Group

Hi Friends

i have a report which gives me details of machines turned on, however i wish to know when was the last time the machine was on network / reported

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
 

This is the present query which displays the following tabs " Machine Name" , "Powered On" , "user logged on ",

i wish to add one more tab which will display when was the last time the machine reported/ turned on

Thank you

Tanoj

Hi Tanoj,

You can have the data with the below query.

select distinct vc.[Name],vc.[User],vc.[OS Name], vc.[IsManaged], vc.[IP address], vc.[Server],  vwrk.maxmodifieddate from symantec_cmdb.dbo.vcomputer VC
Left OUTER JOIN (SELECT  ResourceGuid, MAX(ModifiedDate) AS MaxModifiedDate        
FROM  symantec_cmdb.dbo.ResourceUpdateSummary GROUP BY ResourceGuid) vwrk ON VC.Guid = vwrk.ResourceGuid
 

Thanks,

Rajesh Singh

Thanks Rajesh for the quick reply

the query works for me, however i need one more tab which displays if the machine is turned on or off

please check the attached snapshots , old and new

also the old report shows most of the machines are turned off except one, however your query shows last modified dates which shows most of the machines were/are reporting ( shows latest date, time ), does that mean all the machines are on ?

Thank you

Tanoj

Hi Tanoj,

Maxmodified date is captured from Resource update summary table from which client this must be connected on the network that time. It's is not possible if the machine goes offline after connecting to the server, to say that machine's are online or offline.

Rajesh