:::: MENU ::::

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


So, what do you think ?