Month – February 2011

List PCs within a Security Group

select     SMS_R_SYSTEM.ResourceID,     SMS_R_SYSTEM.ResourceType,     SMS_R_SYSTEM.Name,     SMS_R_SYSTEM.SMSUniqueIdentifier,     SMS_R_SYSTEM.ResourceDomainORWorkgroup,     SMS_R_SYSTEM.Client from     SMS_R_System where     SMS_R_System.SystemGroupName = “GARTEK\\Visio 2007 PC”

v_r_user and v_gs_computer_system

select —    U.ExtensionAttribute50 As ‘Dept #’,     isnull(U.Department0,’Unknown’) As ‘Dept Name’,     isnull(CS.Name0,’Unknown’) as ‘client Id’,     max(AD.AgentTime) as ‘Last Heartbeat’ from     dbo.v_AgentDiscoveries ad     JOIN dbo.v_GS_COMPUTER_SYSTEM cs on AD.ResourceID = CS.ResourceId     join dbo.v_FullCollectionMembership FCM on FCM.ResourceID = CS.ResourceId     Left outer join dbo.V_R_user U ON U.Unique_User_Name0 = CS.UserName0 Where     AD.AgentName = […]

Count of PCs by month and day.

select     DatePart(mm,os.InstallDate0) as ‘Month’,     DATEPART(dd,os.InstallDate0) as ‘Date’,     count(s.resourceid)as ‘daily count’  from      v_GS_OPERATING_SYSTEM OS     Join v_R_System S on S.ResourceId = OS.ResourceID Where     DATEPART(yyyy,os.InstallDate0) =’2010′ group by     DatePart(mm,os.InstallDate0),     DATEPART(dd,os.InstallDate0) order by     DatePart(mm,os.InstallDate0),     DATEPART(dd,os.InstallDate0)

Count of PCs by month and day in 2010

select     DatePart(mm,os.InstallDate0) as ‘Month’,     DATEPART(dd,os.InstallDate0) as ‘Date’,     count(OS.resourceid)as ‘daily count’  from      v_GS_OPERATING_SYSTEM OS Where     DATEPART(yyyy,os.InstallDate0) =’2010′ group by     DatePart(mm,os.InstallDate0),     DATEPART(dd,os.InstallDate0) order by     DatePart(mm,os.InstallDate0),     DATEPART(dd,os.InstallDate0)

PC’s last Heartbeat Date & Time

select     CS.Name0,     max(AD.AgentTime) as ‘Date/Time’ from     dbo.v_AgentDiscoveries ad     JOIN dbo.v_GS_COMPUTER_SYSTEM cs on AD.ResourceID = CS.ResourceId     join dbo.v_FullCollectionMembership FCM on FCM.ResourceID = CS.ResourceId Where     AgentName = ‘Heartbeat Discovery’     and FCM.CollectionID = ‘SMS00001’ Group by     CS.Name0