:::: MENU ::::
Browsing posts in: SCCM Reports ( SCCM SQL Queries)

SQL query that will gives list of all “query based collections”

SELECT TOP (100) PERCENT dbo.v_Collection.Name, dbo.v_Collection.CollectionID, dbo.v_CollectionRuleQuery.RuleName, dbo.v_CollectionRuleQuery.QueryID, dbo.v_CollectionRuleQuery.LimitToCollectionID, dbo.v_CollectionRuleQuery.QueryExpression FROM dbo.v_Collection INNER JOIN dbo.v_CollectionRuleQuery ON dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID ORDER BY dbo.v_Collection.Name, dbo.v_Collection.CollectionID


SCCM Report – Last 30 days Advertisment Status

Note : By default I kept for Last 30 days . You change as per your requirement .It will show up Last 30 days advertisment status

SELECT    DISTINCT v_Package.Name AS ‘Package Name’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID) AS ‘Total Targeted Machines’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘No Status’)) AS ‘No Status machines’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID AND LastState!=0) AS ‘Total Accepted Machines’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Succeeded’, ‘Reboot PendINg’)) AS ‘Total Succeeded Machines’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Failed’)) AS ‘Failed’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Accepted – No Further Status’)) AS ‘Accepted – No Further Status’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Retrying’)) AS ‘Retrying’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Running’)) AS ‘Running’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Waiting’)) AS ‘Waiting’,
v_Advertisement.AdvertisementName AS ‘Advertisement Name’,
v_Advertisement.AdvertisementID AS ‘Advertisement ID’
FROM v_Advertisement
INNER JOIN v_Package ON v_Advertisement.PackageID = v_Package.PackageID
INNER JOIN v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
INNER JOIN v_ClientAdvertisementStatus ON v_Advertisement.AdvertisementID = v_ClientAdvertisementStatus.AdvertisementID
AND v_Advertisement.AdvertisementID in(SELECT advertisementid FROM V_advertisement WHERE DATEDIFF(D, V_advertisement.presenttime,GETDATE())<=30)
ORDER BY v_Advertisement.AdvertisementName


SCCM Report for List of advertisements Advertisment status

This below report will be usefull when your trying to generate the report with mutiple advertisement in single execution

SELECT DISTINCT v_R_System.Name0 AS [System Name], v_R_System.User_Name0 AS [User Name],
vSMS_ClientAdvertisementStatus.AdvertisementID AS [Advertisemnt ID],
v_Advertisement.AdvertisementName,
v_Advertisement.ProgramName,vSMS_ClientAdvertisementStatus.LastStatusMessageIDName AS [Last Status Message],
vSMS_ClientAdvertisementStatus.LastStateName AS [Last Status], vSMS_ClientAdvertisementStatus.Lastexecutionresult
FROM vSMS_ClientAdvertisementStatus INNER JOIN
v_R_System ON vSMS_ClientAdvertisementStatus.ResourceID = v_R_System.ResourceID INNER JOIN
V_Advertisement ON vSMS_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID INNER JOIN
v_RA_System_SMSAssignedSites ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID
WHERE    v_Advertisement.AdvertisementID in(‘ADVERTID1′,’ADVERTID2’)
ORDER BY v_Advertisement.ProgramName


SCCM Collections WQL Query

Below SQL query will show-up the Collections WQL query for specific Collection ID

Day to day operation in order to go and check the collection properties its some what difficult. we can get in side collections WQL query :

 

 

SELECT dbo.v_Collection.Name AS [Collection Name], dbo.Collection_Rules_SQL.WQL AS [WQL Query], dbo.Collection_Rules_SQL.QueryKey AS
Query,dbo.v_CollectionRuleQuery.LimitToCollectionID, dbo.v_Collection.CollectionID FROM  dbo.Collection_Rules_SQL INNER JOIN
dbo.v_Collection ON dbo.Collection_Rules_SQL.CollectionID = dbo.v_Collection.CollID INNER JOIN dbo.v_CollectionRuleQuery ON
dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID AND dbo.Collection_Rules_SQL.QueryKey = dbo.v_CollectionRuleQuery.QueryID
WHERE     (dbo.v_Collection.CollectionID = ‘XXXXXXX’)


Pages:123