Local LastStatusTime in Reports
I noticed this tip in the forums from eyona, http://www.myitforum.com/forums/Help_from_a_Reporting_GURU_Need%3F/m_145775/tm.htm, and it's one of those tidbits of information I know I'll forget if I don't record it somewhere....
As an example below, modified the default report 131 to have an additional column for last status time, translated to the computer's local time.
SELECT adv.AdvertisementName,
stat.LastStateName,
pkg.Name AS C062,
adv.ProgramName AS C071,
adv.AdvertisementID,
pkg.PackageID,
dateadd(ss,@__timezoneoffset,stat.LastStatusTime) [Last Status Time (local)],
stat.LastExecutionResult
FROM v_Advertisement adv
JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
JOIN v_ClientAdvertisementStatus stat ON stat.AdvertisementID = adv.AdvertisementID
JOIN v_R_System sys ON stat.ResourceID=sys.ResourceID
WHERE sys.Netbios_Name0=@ComputerName
Order by adv.AdvertisementName