Monday, 13 October 2014

SQL Report - Package not used recently


SELECT   
      A.AdvertisementName AS [Advertisement Name],
      A.PresentTime AS Created,
      MAX(CAS.LastStatusTime) AS [Last Time Used],
      p.packageID,
      A.ProgramName,
      A.CollectionID,
      P.PkgSourcePath,
            (SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = p.PackageID) AS [No of DP]
FROM
      v_Package P
      INNER JOIN v_Advertisement A ON P.PackageID = A.PackageID
      INNER JOIN v_ClientAdvertisementStatus CAS ON A.AdvertisementID = CAS.AdvertisementID
WHERE
      (CAS.LastStateName != 'Accepted - No Further Status')AND
      p.PackageID NOT IN (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) and p.Packagetype=0
GROUP BY
      A.AdvertisementName,
      A.PresentTime,
      A.CollectionID,
      P.PkgSourcePath,
      P.Name,
      P.PackageID,
      A.ProgramName,
    P.Description
HAVING
      (MAX(CAS.LastStatusTime) IS NOT NULL)
      AND datediff(mm,MAX(CAS.LastStatusTime),getdate()-365) > 0
ORDER BY
      A.AdvertisementName,

      A.PresentTime

No comments:

Post a Comment

📊 SCCM SQL Queries for Server Role Inventory and Client Health Analysis

When working with System Center Configuration Manager (SCCM), having detailed visibility into site systems, their roles, and client health i...