February 2011 - Posts

How to find program that are depend on another program
Friday, February 25, 2011 7:25 PM

select * from dbo.v_Program

Where DependentProgram like '%'+'CMD Admin'+'%'

by Garth Jones

Filed under: ConfigMGr, SMS, Code, SQL, SCCM

List Collection a PC belongs too.
Friday, February 18, 2011 12:48 PM

Select
FCM.CollectionID,
C.Name
from
dbo.v_Collection c
join dbo.v_FullCollectionMembership FCM on C.CollectionID = FCM.CollectionID
Where
FCM.Name = 'HPPC'

by Garth Jones

Filed under: ConfigMGr, SMS, Code, SQL, SCCM

List PCs within a Security Group
Sunday, February 13, 2011 10:39 PM

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"

by Garth Jones

Filed under: ConfigMGr, System Center, Code, WQL, SCCM

v_r_user and v_gs_computer_system
Thursday, February 10, 2011 8:18 AM

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 = 'Heartbeat Discovery'
    and FCM.CollectionID = 'SMS00001'
Group by
--    U.ExtensionAttribute50,
    isnull(U.Department0,'Unknown'),
    isnull(CS.Name0,'Unknown')

by Garth Jones

Filed under: ConfigMGr, SMS, System Center, Code, SQL, SCCM

Count of PCs by month and day in 2010
Friday, February 04, 2011 8:11 AM

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)

by Garth Jones

Filed under: ConfigMGr, System Center, Code, SQL, SCCM

Count of PCs by month and day.
Friday, February 04, 2011 8:10 AM

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)

by Garth Jones

Filed under: ConfigMGr, System Center, Code, SQL, SCCM

PC’s last Heartbeat Date & Time
Thursday, February 03, 2011 10:13 PM

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

by Garth Jones

Filed under: ConfigMGr, System Center, Code, SQL, SCCM, vNext