SQL views
Among the various views available in SCCM 2007 database, there are two identical(yet different) types of views, HS and GS.
Here GS refers to Current data and HS refers to Historical data.
Knowing this comes handy when choosing the correct view to be queried on a report.
SQL reports
Get update lists per
software update KB (all variants)
select DisplayName,
CI_ID from fn_ListAuthListCIs(1033) AS sul where sul.CI_ID in (
select rel.FromCIID
from v_CIRelation
AS rel where
rel.ToCIID IN (select CI_ID from fn_ListUpdateCIs(1033) AS su where
su.ArticleID='4054522'))
Get domain of all clients
select Name0,Domain0
from v_GS_COMPUTER_SYSTEM
Last patched date for all servers
select * from (
select
PS.LastStatusTime, PS.LastStateName, SYS.Name0, OS.LastBootUpTime0,
ROW_NUMBER()
OVER(PARTITION BY SYS.Name0 ORDER BY PS.LastStatusTime DESC) rn
from
v_gs_patchstatusEx PS
inner join
v_R_System SYS ON PS.ResourceID = SYS.ResourceID
inner join
v_GS_OPERATING_SYSTEM OS ON SYS.ResourceID = OS.ResourceID
where LastStateName
= 'Install Verified' AND (PS.LastStatusTime < Getdate())
AND PS.isActive =
'1') a where rn=1 ORDER BY LastBootUpTime0 DESC
Collection membership for certain client
select
v_FullCollectionMembership.CollectionID As 'Collection ID', v_Collection.Name
As 'Collection Name', v_R_System.Name0 As 'Machine Name' from
v_FullCollectionMembership
JOIN v_R_System on
v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN v_Collection on
v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
Where
v_R_System.Name0= '<hostname>'
Update lists applicable for a
certain collection (ID)
select distinct
UL.Title, case when ULL.Status='1' then 'Not Required'
when ULL.Status='2'
then 'Required' when ULL.Status='3' then 'Installed'
end as 'Install
Status'
FROM
v_UpdateListStatus_Live ULL
INNER JOIN
v_AuthListInfo UL ON ULL.CI_ID=UL.CI_ID
INNER JOIN
v_R_System SYST ON SYST.ResourceID=ULL.ResourceID
INNER JOIN
v_FullCollectionMembership CM on SYST.ResourceID=CM.ResourceID
where
CM.CollectionID = '<collection ID>' AND UL.Title NOT LIKE '%SQL%'
AND UL.Title NOT
LIKE '%CSA%' AND UL.Title LIKE '%(APPROVED)%'
AND ULL.Status = '2'
WQL queries
Client Systems installed
within the last 48 hours
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 inner join
SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId =
SMS_R_System.ResourceId inner join
SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId
where (DateDiff(hh, SMS_G_System_OPERATING_SYSTEM.InstallDate, GetDate()) <
48) and SMS_G_System_SYSTEM.SystemRole = "Server" and
SMS_G_System_OPERATING_SYSTEM.InstallDate <= GetDate()
