SCCM Knowledge and Sharing: SCCM 2007 / 2012 SQL Queries

General

Notes

Grant Select On Folders To smsschm_users, webreport_approle
Grant Select On FolderMembers To smsschm_users, webreport_approle
Grant Select On vFolderMembers To smsschm_users, webreport_approle
Grant Select On vSMS_Folders To smsschm_users, webreport_approle

Revoke Select On FolderMembers To smsschm_users, webreport_approle
Revoke Select On Folders To smsschm_users, webreport_approle
Revoke Select On vFolderMembers To smsschm_users, webreport_approle
Revoke Select On vSMS_Folders To smsschm_users, webreport_approle

SELECT SYSTEM_USER
SELECT USER_NAME() AS ApplicationRole, SUSER_SNAME() AS LoginName
SELECT SYSTEM_USER, USER_NAME(), SUSER_NAME()
webreport_approle - select
smsschm_users - select

execute as user = 'domain\username'
EXECUTE AS LOGIN = 'login1';
SELECT * FROM Table

Forumns

Reporting

SCCM Queries

Query 1: SCCM Advertisement Status (SCCM Report)

Prompt Properties: 

Name: AdvName
Prompt Text: Advertisement Name

declare @Total int
declare @Accepted int

begin
 if (@__filterwildcard = '')
  select AdvertisementName, AdvertisementID, Comment from v_Advertisement order by AdvertisementName
 else
  select AdvertisementName, AdvertisementID, Comment from v_Advertisement
  WHERE AdvertisementID like @__filterwildcard
  order by AdvertisementName
end

-- Report --

declare @Total int
declare @Accepted int

select @Total=count(*), @Accepted=sum(case LastState when 0 then 0 else 1 end)
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
select LastAcceptanceStateName as 'Status', count(*) as 'Number of Resources',
ROUND(100.0*count(*)/@Total,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastAcceptanceStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
select LastStateName as 'Status of Targeted Resources', count(*) as 'Number of Resources',
ROUND(100.0*count(*)/@Accepted,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
SELECT a.Netbios_name0 as 'Host Name', a.Resource_Domain_OR_Workgr0,
site.sms_installed_sites0 as 'Sitecode',
a.Client0,
a.Obsolete0,
adv.AdvertisementName,
adv.AdvertisementID,
pkg.Name AS 'Package Name',
adv.ProgramName,
advstate.LastAcceptanceStatusTime,
advstate.LastAcceptanceStateName,
advstate.LastAcceptanceMessageIDname,
advstate.LastStatusmessageIDName,
advstate.LaststateName,
advstate.LastExecutionResult,
advstate.LastStatusTime,
advstate.LastExecutionContext
FROM v_Advertisement adv
INNER JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
INNER JOIN v_ClientAdvertisementStatus advstate on adv.AdvertisementID=advstate.AdvertisementID
INNER JOIN V_R_SYSTEM a ON a.Resourceid=advstate.resourceid
INNER JOIN v_GS_WORKSTATION_STATUS HW ON a.resourceid=hw.resourceid
INNER JOIN v_GS_LastSoftwareScan sw ON a.resourceid=sw.resourceid
LEFT OUTER JOIN v_RA_System_SMSInstalledSites site ON a.resourceid=site.resourceid
WHERE ADV.AdvertisementName like @AdvName
order by advstate.LaststateName

Query 2: SCCM Advertisement Status

From http://www.classictriple.com/sccm-the-best-advertisement-status-report/

declare @Total int
declare @Accepted int
declare @AdvName VARCHAR(100)

set @AdvName = 'Change Me'

select @Total=count(*), @Accepted=sum(case LastState when 0 then 0 else 1 end)
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
select LastAcceptanceStateName as 'Status', count(*) as 'Number of Resources',
ROUND(100.0*count(*)/@Total,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastAcceptanceStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
select LastStateName as 'Status of Targeted Resources', count(*) as 'Number of Resources',
ROUND(100.0*count(*)/@Accepted,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
SELECT a.Netbios_name0 as 'Host Name', a.Resource_Domain_OR_Workgr0,
site.sms_installed_sites0 as 'Sitecode',
a.Client0,
a.Obsolete0,
adv.AdvertisementName,
adv.AdvertisementID,
pkg.Name AS 'Package Name',
adv.ProgramName,
advstate.LastAcceptanceStatusTime,
advstate.LastAcceptanceStateName,
advstate.LastAcceptanceMessageIDname,
advstate.LastStatusmessageIDName,
advstate.LaststateName,
advstate.LastExecutionResult,
advstate.LastStatusTime,
advstate.LastExecutionContext
FROM v_Advertisement adv
INNER JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
INNER JOIN v_ClientAdvertisementStatus advstate on adv.AdvertisementID=advstate.AdvertisementID
INNER JOIN V_R_SYSTEM a ON a.Resourceid=advstate.resourceid
INNER JOIN v_GS_WORKSTATION_STATUS HW ON a.resourceid=hw.resourceid
INNER JOIN v_GS_LastSoftwareScan sw ON a.resourceid=sw.resourceid
LEFT OUTER JOIN v_RA_System_SMSInstalledSites site ON a.resourceid=site.resourceid
WHERE ADV.AdvertisementName like @AdvName
order by advstate.LaststateName

Query 3: SCCM Advertisement, Package Type and Program Information with Target Count

select CollectionID, COUNT(*) as MemberCount into #TempTable from v_FullCollectionMembership group by CollectionID 

SELECT     dbo.v_Advertisement.AdvertisementID, 
           dbo.v_Advertisement.AdvertisementName,
           'PackageType' =
 Case
  when v_Package.PackageType = 0 Then 'Software Distribution Package'
  when v_Package.PackageType = 3 Then 'Driver Package'
  when v_Package.PackageType = 4 Then 'Task Sequence Package'
  when v_Package.PackageType = 5 Then 'Software Update Package'
  when v_Package.PackageType = 6 Then 'Device Setting Package'
  when v_Package.PackageType = 7 Then 'Virtual Package'
                when v_Package.PackageType = 8 Then 'Application'
  when v_Package.PackageType = 257 Then 'Image Package'
  when v_Package.PackageType = 258 Then 'Boot Image Package'
  when v_Package.PackageType = 259 Then 'Operating System Install Package'
 Else
  'Unknown'
 End, 
        v_Collection.CollectionID,
        v_Collection.Name as CollectionName,
        #TempTable.MemberCount,
           dbo.v_Advertisement.PackageID, 
           dbo.v_Package.Name as PackageName, 
           dbo.v_Package.PkgSourcePath,            
           dbo.v_Advertisement.ProgramName, 
           dbo.v_Program.CommandLine
 
FROM         dbo.v_Advertisement INNER JOIN
                      dbo.v_Program ON dbo.v_Advertisement.PackageID = dbo.v_Program.PackageID AND 
                      dbo.v_Advertisement.ProgramName = dbo.v_Program.ProgramName INNER JOIN
                      dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
                      dbo.v_Collection ON dbo.v_Advertisement.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
                      #TempTable ON dbo.v_Advertisement.CollectionID = #TempTable.CollectionID

drop table #TempTable

Query 4: SCCM Adveritsment, Package Type and Program Information

SELECT     dbo.v_Advertisement.AdvertisementID, 
           dbo.v_Advertisement.AdvertisementName,
           'PackageType' =
 Case
  when v_Package.PackageType = 0 Then 'Software Distribution Package'
  when v_Package.PackageType = 3 Then 'Driver Package'
  when v_Package.PackageType = 4 Then 'Task Sequence Package'
  when v_Package.PackageType = 5 Then 'Software Update Package'
  when v_Package.PackageType = 6 Then 'Device Setting Package'
  when v_Package.PackageType = 7 Then 'Virtual Package'
                when v_Package.PackageType = 8 Then 'Application'
  when v_Package.PackageType = 257 Then 'Image Package'
  when v_Package.PackageType = 258 Then 'Boot Image Package'
  when v_Package.PackageType = 259 Then 'Operating System Install Package'
 Else
  'Unknown'
 End, 
        v_Collection.CollectionID,
        v_Collection.Name as CollectionName,
           dbo.v_Advertisement.PackageID, 
           dbo.v_Package.Name as PackageName, 
           dbo.v_Package.PkgSourcePath,            

           dbo.v_Advertisement.ProgramName, 
           dbo.v_Program.CommandLine
 
FROM         dbo.v_Advertisement INNER JOIN
                      dbo.v_Program ON dbo.v_Advertisement.PackageID = dbo.v_Program.PackageID AND 
                      dbo.v_Advertisement.ProgramName = dbo.v_Program.ProgramName INNER JOIN
                      dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
                      dbo.v_Collection ON dbo.v_Advertisement.CollectionID = dbo.v_Collection.CollectionID

Query 4a: SCCM Adveritsment, Package Type and Program Information - task sequences only

SELECT     dbo.v_Advertisement.AdvertisementID, 
           dbo.v_Advertisement.AdvertisementName,
           'PackageType' =
 Case
  when v_Package.PackageType = 0 Then 'Software Distribution Package'
  when v_Package.PackageType = 3 Then 'Driver Package'
  when v_Package.PackageType = 4 Then 'Task Sequence Package'
  when v_Package.PackageType = 5 Then 'Software Update Package'
  when v_Package.PackageType = 6 Then 'Device Setting Package'
  when v_Package.PackageType = 7 Then 'Virtual Package'
                when v_Package.PackageType = 8 Then 'Application'
  when v_Package.PackageType = 257 Then 'Image Package'
  when v_Package.PackageType = 258 Then 'Boot Image Package'
  when v_Package.PackageType = 259 Then 'Operating System Install Package'
 Else
  'Unknown'
 End, 
        v_Collection.CollectionID,
        v_Collection.Name as CollectionName,
           dbo.v_Advertisement.PackageID, 
           dbo.v_Package.Name as PackageName, 
           dbo.v_Package.PkgSourcePath,            

           dbo.v_Advertisement.ProgramName, 
           dbo.v_Program.CommandLine
 
FROM         dbo.v_Advertisement INNER JOIN
                      dbo.v_Program ON dbo.v_Advertisement.PackageID = dbo.v_Program.PackageID AND 
                      dbo.v_Advertisement.ProgramName = dbo.v_Program.ProgramName INNER JOIN
                      dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
                      dbo.v_Collection ON dbo.v_Advertisement.CollectionID = dbo.v_Collection.CollectionID
where v_Package.PackageType = 4

Query 5: SCCM Package and Package Type

select *,
'Package Type (Text)' =
 Case
  when v_Package.PackageType = 0 Then 'Software Distribution Package'
  when v_Package.PackageType = 3 Then 'Driver Package'
  when v_Package.PackageType = 4 Then 'Task Sequence Package'
  when v_Package.PackageType = 5 Then 'Software Update Package'
  when v_Package.PackageType = 6 Then 'Device Setting Package'
  when v_Package.PackageType = 7 Then 'Virtual Package'
  when v_Package.PackageType = 8 Then 'Application'
  when v_Package.PackageType = 257 Then 'Image Package'
  when v_Package.PackageType = 258 Then 'Boot Image Package'
  when v_Package.PackageType = 259 Then 'Operating System Install Package'
 Else
  'Unknown'
 End
from v_Package

Query 6: SCCM Advertisements showing Advertisement Path, Collection Path and Packge Path and Package Type

-- SCCM Advertisements showing Advertisement Path, Collection Path and Packge Path and Package Type  --

;WITH folderHierarchy_Collections(CollectionID,[Name],ParentCollectionID,[Path])
AS
(
      SELECT 
            CollectionID, 
            [Name], 
            ParentCollectionID,
            CAST('/'+[Name]+'/' AS VARCHAR(MAX)) AS [Path]
      FROM
            (SELECT 
                  CollectionID, 
                  [Name],
                  ParentCollectionID 
             FROM v_Collection
                  INNER JOIN v_CollectToSubCollect 
                  ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1
      WHERE
            ParentCollectionID = 'COLLROOT'

    UNION ALL
 
   SELECT
      child.CollectionID,
      child.Name,
      child.ParentCollectionID,
      parent.[Path]+child.[Name]+'/' AS [Path]
   FROM
      (SELECT 
                  CollectionID, 
                  [Name], 
                  ParentCollectionID
             FROM v_Collection
                  INNER JOIN v_CollectToSubCollect 
                  ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS child
      INNER JOIN folderHierarchy_Collections AS parent
         ON parent.CollectionID = child.ParentCollectionID
),
FolderHierarchy_Packages(ParentContainerNodeID, ContainerNodeID, name, Level) as (select f.ParentContainerNodeID, f.ContainerNodeID, cast(f.name+ '/' as
varchar(max)) as Name, 0 AS Level
from dbo.Folders as f
where f.ParentContainerNodeID=0

union all

select f.ParentContainerNodeID, f.ContainerNodeID, fh.name+f.Name+'/',
Level+1
from dbo.Folders as f
inner join FolderHierarchy_Packages as fh
on f.ParentContainerNodeID=fh.ContainerNodeID
),

FolderHierarchy_Advertisements(ParentContainerNodeID, ContainerNodeID, name, Level) as ( select f.ParentContainerNodeID, f.ContainerNodeID, cast(f.name+ '/' as
varchar(max)) as Name, 0 AS Level
from dbo.Folders as f
where f.ParentContainerNodeID=0

union all

select f.ParentContainerNodeID, f.ContainerNodeID, fh.name+f.Name+'/',
Level+1
from dbo.Folders as f
inner join FolderHierarchy_Advertisements as fh
on f.ParentContainerNodeID=fh.ContainerNodeID
)

SELECT * --<<-- ackage="" advertisement_path="" advertisements="" as="" ask="" case="" collection="" collection_name="" collection_path="" collectionname="" columns="" combined1.advertisementid="advertisements.AdvertisementID" combined1.collectionid="[collection].CollectionID" combined1.packageid="packages.PackageID" combined1="" dbo.foldermembers="" distribution="" else="" end="" evice="" ext="" fh.containernodeid="fm.ContainerNodeID" fh.name="" fh="" fldr.collectionid="" fldr.name="" fldr.path="" fldr="" fm.instancekey="v_Advertisement.AdvertisementID" fm="" folderhierarchy_advertisements="" folderhierarchy_collections="" folderhierarchy_packages="" from="" image="" inner="" install="" irtual="" join="" mage="" need="" nknown="" oftware="" on="" oot="" outer="" package="" package_name="" package_path="" packages="" perating="" pre="" right="" river="" select="" sequence="" setting="" specific="" system="" the="" then="" to="" type="" update="" v_advertisement.advertisementid="" v_advertisement.advertisementname="" v_advertisement.collectionid="v_Collection.CollectionID" v_advertisement.packageid="v_Package.PackageID" v_advertisement="" v_collection.collectionid="" v_collection.name="" v_collection="" v_package.name="" v_package.packageid="" v_package.packagetype="259" v_package="" when="" you="">
Query 7: SCCM Advertisements showing Advertisement Path, Collection Path and Packge Path
-- SCCM Advertisements showing Advertisement Path, Collection Path and Packge Path --

WITH folderHierarchy_Collections(CollectionID,[Name],ParentCollectionID,[Path])
AS
(
      SELECT 
            CollectionID, 
            [Name], 
            ParentCollectionID,
            CAST('/'+[Name]+'/' AS VARCHAR(MAX)) AS [Path]
      FROM
            (SELECT 
                  CollectionID, 
                  [Name],
                  ParentCollectionID 
             FROM v_Collection
                  INNER JOIN v_CollectToSubCollect 
                  ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1
      WHERE
            ParentCollectionID = 'COLLROOT'

    UNION ALL
 
   SELECT
      child.CollectionID,
      child.Name,
      child.ParentCollectionID,
      parent.[Path]+child.[Name]+'/' AS [Path]
   FROM
      (SELECT 
                  CollectionID, 
                  [Name], 
                  ParentCollectionID
             FROM v_Collection
                  INNER JOIN v_CollectToSubCollect 
                  ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS child
      INNER JOIN folderHierarchy_Collections AS parent
         ON parent.CollectionID = child.ParentCollectionID
),
FolderHierarchy_Packages(ParentContainerNodeID, ContainerNodeID, name, Level) as (select f.ParentContainerNodeID, f.ContainerNodeID, cast(f.name+ '/' as
varchar(max)) as Name, 0 AS Level
from dbo.Folders as f
where f.ParentContainerNodeID=0

union all

select f.ParentContainerNodeID, f.ContainerNodeID, fh.name+f.Name+'/',
Level+1
from dbo.Folders as f
inner join FolderHierarchy_Packages as fh
on f.ParentContainerNodeID=fh.ContainerNodeID
),

FolderHierarchy_Advertisements(ParentContainerNodeID, ContainerNodeID, name, Level) as ( select f.ParentContainerNodeID, f.ContainerNodeID, cast(f.name+ '/' as
varchar(max)) as Name, 0 AS Level
from dbo.Folders as f
where f.ParentContainerNodeID=0

union all

select f.ParentContainerNodeID, f.ContainerNodeID, fh.name+f.Name+'/',
Level+1
from dbo.Folders as f
inner join FolderHierarchy_Advertisements as fh
on f.ParentContainerNodeID=fh.ContainerNodeID
)

SELECT * --<<-- advertisement_path="" advertisements="" as="" collection="" collection_name="" collection_path="" collectionname="" columns="" combined1.advertisementid="advertisements.AdvertisementID" combined1.collectionid="[collection].CollectionID" combined1.packageid="packages.PackageID" combined1="" dbo.foldermembers="" fh.containernodeid="fm.ContainerNodeID" fh.name="" fh="" fldr.collectionid="" fldr.name="" fldr.path="" fldr="" fm.instancekey="v_Advertisement.AdvertisementID" fm="" folderhierarchy_advertisements="" folderhierarchy_collections="" folderhierarchy_packages="" from="" inner="" join="" need="" on="" outer="" package_name="" package_path="" packages="" pre="" right="" select="" specific="" the="" to="" v_advertisement.advertisementid="" v_advertisement.advertisementname="" v_advertisement.collectionid="v_Collection.CollectionID" v_advertisement.packageid="v_Package.PackageID" v_advertisement="" v_collection.collectionid="" v_collection.name="" v_collection="" v_package.name="" v_package.packageid="" v_package="" you="">
Query 8: SCCM Packages with Path
--- SCCM Packages with Path ---

with FolderHierarchy_Packages(ParentContainerNodeID, ContainerNodeID, name, Level) as (select f.ParentContainerNodeID, f.ContainerNodeID, cast(f.name+ '/' as
varchar(max)) as Name, 0 AS Level
from dbo.Folders as f
where f.ParentContainerNodeID=0

union all

select f.ParentContainerNodeID, f.ContainerNodeID, fh.name+f.Name+'/',
Level+1
from dbo.Folders as f
inner join FolderHierarchy_Packages as fh
on f.ParentContainerNodeID=fh.ContainerNodeID
)

select v_Package.PackageID, v_Package.Name as Package_Name, fh.name as Package_Path  from FolderHierarchy_Packages as fh inner join dbo.FolderMembers as fm on fh.ContainerNodeID = fm.ContainerNodeID right outer join v_Package on fm.InstanceKey = v_Package.PackageID



Query 9: SCCM Advertisements with Path
--- SCCM Advertisements with Path ---

;with FolderHierarchy_Advertisements(ParentContainerNodeID, ContainerNodeID, name, Level) as ( select f.ParentContainerNodeID, f.ContainerNodeID, cast(f.name+ '/' as
varchar(max)) as Name, 0 AS Level
from dbo.Folders as f
where f.ParentContainerNodeID=0

union all

select f.ParentContainerNodeID, f.ContainerNodeID, fh.name+f.Name+'/',
Level+1
from dbo.Folders as f
inner join FolderHierarchy_Advertisements as fh
on f.ParentContainerNodeID=fh.ContainerNodeID
)

select v_Advertisement.AdvertisementID, v_Advertisement.AdvertisementName, fh.name as Advertisement_Path from FolderHierarchy_Advertisements as fh inner join dbo.FolderMembers as fm on fh.ContainerNodeID = fm.ContainerNodeID right outer join v_Advertisement on fm.InstanceKey = v_Advertisement.AdvertisementID



Query 10: SCCM Collections with Path (Working)
WITH folderHierarchy (CollectionID,[Name],ParentCollectionID,[Path])
 AS
 (
 
      SELECT 
            CollectionID, 
            [Name], 
            ParentCollectionID,
             CAST('/'+[Name]+'/' AS VARCHAR(MAX)) AS [Path]
       FROM
             (SELECT 
                  CollectionID, 
                  [Name], 
                  ParentCollectionID 
             FROM v_Collection
                   INNER JOIN v_CollectToSubCollect 
                  ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1
       WHERE
             ParentCollectionID = 'COLLROOT'
 
    UNION ALL
  
    SELECT
       child.CollectionID,
       child.Name,
       child.ParentCollectionID,
       parent.[Path]+child.[Name]+'/' AS [Path]
    FROM
       (SELECT 
                  CollectionID, 
                  [Name], 
                  ParentCollectionID
              FROM v_Collection
                   INNER JOIN v_CollectToSubCollect 
                  ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS child
       INNER JOIN folderHierarchy AS parent
          ON parent.CollectionID = child.ParentCollectionID
 )
 SELECT 
   fldr.[Path],
    fldr.[Name],
    fldr.CollectionID
 
FROM
    folderHierarchy AS fldr


Query 11: SCCM Collections with Path
-- SCCM Collections with Path --

;WITH folderHierarchy_Collections(CollectionID,[Name],ParentCollectionID,[Path])
AS
(
      SELECT 
            CollectionID, 
            [Name], 
            ParentCollectionID,
            CAST('/'+[Name]+'/' AS VARCHAR(MAX)) AS [Path]
      FROM
            (SELECT 
                  CollectionID, 
                  [Name],
                  ParentCollectionID 
             FROM v_Collection
                  INNER JOIN v_CollectToSubCollect 
                  ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1
      WHERE
            ParentCollectionID = 'COLLROOT'

    UNION ALL
 
   SELECT
      child.CollectionID,
      child.Name,
      child.ParentCollectionID,
      parent.[Path]+child.[Name]+'/' AS [Path]
   FROM
      (SELECT 
                  CollectionID, 
                  [Name], 
                  ParentCollectionID
             FROM v_Collection
                  INNER JOIN v_CollectToSubCollect 
                  ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS child
      INNER JOIN folderHierarchy_Collections AS parent
         ON parent.CollectionID = child.ParentCollectionID
)
SELECT     fldr.CollectionID, fldr.Name as Collection_Name, fldr.Path as Collection_Path
FROM         folderHierarchy_Collections AS fldr
ORDER BY fldr.Name



Query Group 11: Check Machines for Computer Association
SELECT     v_R_System.ResourceID, v_R_System.Name0, 
     v_R_System.Client0, v_R_System.Client_Version0, 
     v_R_System.Active0, v_GS_OPERATING_SYSTEM.Caption0, 
           v_GS_OPERATING_SYSTEM.CSDVersion0, 
     v_RA_System_MACAddresses.MAC_Addresses0
FROM       v_R_System INNER JOIN
           v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
           v_RA_System_MACAddresses ON v_R_System.ResourceID = v_RA_System_MACAddresses.ResourceID
WHERE
     v_R_System.Name0 = 'XXX'
     
select * from v_FullCollectionMembership where CollectionID = 'XXX'

select * from v_CollectionRuleDirect where CollectionID = 'XXX'


Query 11a: Lookup Machine based on MAC Address
-- Query with OS Details --

SELECT     v_R_System.ResourceID, v_R_System.Name0, 
     v_R_System.Client0, v_R_System.Client_Version0, 
     v_R_System.Active0, v_GS_OPERATING_SYSTEM.Caption0, 
           v_GS_OPERATING_SYSTEM.CSDVersion0, 
     v_RA_System_MACAddresses.MAC_Addresses0
FROM       v_R_System INNER JOIN
           v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
           v_RA_System_MACAddresses ON v_R_System.ResourceID = v_RA_System_MACAddresses.ResourceID
WHERE
           v_RA_System_MACAddresses.MAC_Addresses0 = '00:00:00:00:00:00'

-- Query without OS Details --

SELECT     v_R_System.ResourceID, v_R_System.Name0, 
     v_R_System.Client0, v_R_System.Client_Version0, 
     v_R_System.Active0, 
     v_RA_System_MACAddresses.MAC_Addresses0
FROM       v_R_System INNER JOIN           
           v_RA_System_MACAddresses ON v_R_System.ResourceID = v_RA_System_MACAddresses.ResourceID
WHERE
           v_RA_System_MACAddresses.MAC_Addresses0 = '00:00:00:00:00:00'


Query 11b: Lookup Machine and return MAC address details
SELECT     v_R_System.ResourceID, v_R_System.Name0, 
     v_R_System.Client0, v_R_System.Client_Version0, 
     v_R_System.Active0, v_GS_OPERATING_SYSTEM.Caption0, 
           v_GS_OPERATING_SYSTEM.CSDVersion0, 
     v_RA_System_MACAddresses.MAC_Addresses0
FROM       v_R_System INNER JOIN
           v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
           v_RA_System_MACAddresses ON v_R_System.ResourceID = v_RA_System_MACAddresses.ResourceID
WHERE
    v_R_System.Name0 like 'TEST%'


Query 11c: Check MAC Address Table
select * from v_RA_System_MACAddresses where v_RA_System_MACAddresses.MAC_Addresses0 = '00:00:00:00:00:00'


Query 12: Lookup users Machine
SELECT     v_R_System.ResourceID, v_R_System.Netbios_Name0, v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0
FROM       v_R_System INNER JOIN
           v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE      (v_R_System.User_Name0 = 'xxx')



Query 12: Lookup users Machine
SELECT     v_R_System.ResourceID, v_R_System.Netbios_Name0, v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0
FROM       v_R_System INNER JOIN
           v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE      (v_R_System.User_Name0 = 'xxx')



Query 13: List computers marked as 'Unknown'
SELECT     v_R_System.Name0, v_R_System.ResourceID, v_RA_System_MACAddresses.MAC_Addresses0, v_R_System.Unknown0
FROM         v_R_System INNER JOIN
                      v_RA_System_MACAddresses ON v_R_System.ResourceID = v_RA_System_MACAddresses.ResourceID
WHERE     (v_R_System.Name0 = 'Unknown')


Query 14: Collection Analysis Group
select * from v_CollectionRuleDirect
select * from v_CollectionRuleQuery
select * from Collection_Rules_SQL
select * from Collection_Rules


Query 15: Collection Membership Analysis

SCCM 2012
SELECT     v_FullCollectionMembership.CollectionID, v_Collection.Name AS Collection_Name, v_FullCollectionMembership.Name AS Machine_Name, 
                      v_R_System.AD_Site_Name0, v_R_System.Client0, v_R_System.Client_Version0, v_FullCollectionMembership.IsDirect, v_FullCollectionMembership.IsAssigned, 
                      v_FullCollectionMembership.IsClient, v_FullCollectionMembership.ClientType, v_FullCollectionMembership.IsObsolete, v_FullCollectionMembership.IsActive, 
                      v_FullCollectionMembership.IsDecommissioned, v_FullCollectionMembership.IsVirtualMachine, v_FullCollectionMembership.VMHostName
FROM         v_FullCollectionMembership INNER JOIN
                      v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID INNER JOIN
                      v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
WHERE     (v_FullCollectionMembership.Name LIKE 'XXX')


SCCM 2007
SELECT     v_FullCollectionMembership.CollectionID, v_Collection.Name AS Collection_Name, v_FullCollectionMembership.Name AS Machine_Name, 
                      v_R_System.AD_Site_Name0, v_R_System.Client0, v_R_System.Client_Version0, v_FullCollectionMembership.IsDirect, v_FullCollectionMembership.IsAssigned, 
                      v_FullCollectionMembership.IsClient, v_FullCollectionMembership.ClientType, v_FullCollectionMembership.IsObsolete, v_FullCollectionMembership.IsActive, 
                      v_FullCollectionMembership.IsDecommissioned
FROM         v_FullCollectionMembership INNER JOIN
                      v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID INNER JOIN
                      v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
WHERE     (v_FullCollectionMembership.Name LIKE 'XXX')


Query 16: Security Patch Queries Group
-- All Updates for a particular machine --

declare @RscID int; 
select @RscID=ResourceID from v_R_System where ((Name0 = 'LEVEL1') and (Active0 = 1));

select 
   catinfo.CategoryInstanceName as Vendor,
   catinfo2.CategoryInstanceName as UpdateClassification,
            ui.BulletinID as BulletinID,
            ui.ArticleID as ArticleID,
            ui.Title as Title,            
            Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
            Installed=(case when css.Status=3 then '*' else '' end),
            IsRequired=(case when css.Status=2 then '*' else '' end),
            Deadline=cdl.Deadline,
            ui.CI_UniqueID as UniqueUpdateID,
   ui.InfoURL as InformationURL
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_CICategories_All catall on catall.CI_ID=ui.CI_ID 
join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 
join v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
left join (
                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
where  css.ResourceID = @RscID 
and ((css.Status=2) or (css.Status=3))
order by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID

-- All Updates for a particular machine --

declare @RscID int; 
select @RscID=ResourceID from v_R_System where ((Name0 = 'LEVEL1') and (Active0 = 1));

select 
   catinfo.CategoryInstanceName as Vendor,
   catinfo2.CategoryInstanceName as UpdateClassification,
            ui.BulletinID as BulletinID,
            ui.ArticleID as ArticleID,
            ui.Title as Title,            
            Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
            Installed=(case when css.Status=3 then '*' else '' end),
            IsRequired=(case when css.Status=2 then '*' else '' end),
            Deadline=cdl.Deadline,
            ui.CI_UniqueID as UniqueUpdateID,
   ui.InfoURL as InformationURL
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_CICategories_All catall on catall.CI_ID=ui.CI_ID 
join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 
join v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
left join (
                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
where  css.ResourceID = @RscID 
and css.Status=3
order by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID


Query 17: Misc Queries
SELECT DISTINCT v_R_System.ResourceID, v_R_System.Netbios_Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_WORKSTATION_STATUS.LastHWScan
FROM       v_GS_ADD_REMOVE_PROGRAMS 
INNER JOIN v_R_System ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID 
INNER JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
WHERE ((v_R_System.Netbios_Name0 = 'PC1') AND 
((v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '% Visio %') OR (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '% Project %'))
)
ORDER BY v_R_System.Netbios_Name0


Query 18: SCCM Driver Queries
select * from v_CategoryInfo

SELECT     CategoryInstanceID, DateLastModified, CategoryInstanceName, CategoryTypeName
FROM         v_CategoryInfo
WHERE     (CategoryTypeName = 'DriverCategories')

SELECT DISTINCT CategoryInstanceName FROM v_CategoryInfo WHERE (CategoryTypeName = 'DriverCategories')


Query 19: Add Remove Programs Search
SELECT     v_R_System.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_R_System.AD_Site_Name0, v_GS_OPERATING_SYSTEM.Caption0
FROM         v_GS_ADD_REMOVE_PROGRAMS INNER JOIN
                      v_R_System ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID INNER JOIN
                      v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE     (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE 'Compatibility Pack for the 2007 Office system')


Query 20: Basic Resource Data
SELECT     v_R_System.ResourceID, v_R_System.Name0, v_R_System.AD_Site_Name0, v_R_System.Active0, v_R_System.Client0, v_R_System.Client_Version0, 
                      v_R_System.Obsolete0, v_GS_OPERATING_SYSTEM.Caption0
FROM         v_R_System INNER JOIN
                      v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID


Query 21 : Distribution Status - Percent complete (SCCM 2012)
select SubString(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath)+2,(CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath))-3) AS ServerName, pkg.PackageID,
pkg.Manufacturer, pkg.Name, pkg.Version, stat.SourceVersion,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), stat.SummaryDate) AS LastRefreshTime,
 (select top 1 msg.InsString3
       from [dbo].[v_StatMsgWithInsStrings] msg
       JOIN [dbo].[v_StatMsgModuleNames] modNames on msg.ModuleName = modNames.ModuleName
       JOIN [dbo].[v_StatMsgAttributes] attpkg on msg.RecordID=attpkg.RecordID and msg.Time=attpkg.AttributeTime
       JOIN [dbo].[v_StatMsgAttributes] attdp on msg.RecordID=attdp.RecordID and msg.Time=attdp.AttributeTime
     where attpkg.AttributeValue =pkg.PackageID and msg.MessageID = '8204' and msg.InsString2 =SubString(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath)+2,(CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath))-3)
     order by msg.Time desc) AS '% Completed',
  stat.InstallStatus
from v_Package pkg
JOIN v_DistributionPoint dp ON pkg.PackageID=dp.PackageID
JOIN v_PackageStatusDistPointsSumm stat ON dp.ServerNALPath=stat.ServerNALPath
AND dp.PackageID=stat.PackageID
where stat.State!=0
order by ServerName


Query 22 : Distribution Status - percent complete of in-progress packages (SCCM 2012)
-- Content View All Packages --

-- WQL
SELECT * FROM SMS_ObjectContentInfo
-- SQL 
select  all SMS_ObjectContentInfo.DateCreated,SMS_ObjectContentInfo.Description,SMS_ObjectContentInfo.FeatureType,SMS_ObjectContentInfo.LastUpdateDate,SMS_ObjectContentInfo.NumberErrors,SMS_ObjectContentInfo.NumberInProgress,SMS_ObjectContentInfo.NumberInstalled,SMS_ObjectContentInfo.NumberUnknown,SMS_ObjectContentInfo.ObjectID,SMS_ObjectContentInfo.ObjectType,SMS_ObjectContentInfo.ObjectTypeID,SMS_ObjectContentInfo.PackageID,SMS_ObjectContentInfo.SoftwareName,SMS_ObjectContentInfo.SourceSite,SMS_ObjectContentInfo.SourceSize,SMS_ObjectContentInfo.SourceVersion,SMS_ObjectContentInfo.Targeted from fn_ListObjectContentInfo(1033) AS SMS_ObjectContentInfo

-- Content View Specific Package --
 
-- Success (Installed) --

-- WQL
SELECT * FROM SMS_DistributionDPStatus WHERE PackageID = 'XXX' AND MessageCategory = '31'
-- SQL
select  all SMS_DistributionDPStatus.GroupCount,SMS_DistributionDPStatus.ID,SMS_DistributionDPStatus.InsString1,SMS_DistributionDPStatus.InsString10,SMS_DistributionDPStatus.InsString2,SMS_DistributionDPStatus.InsString3,SMS_DistributionDPStatus.InsString4,SMS_DistributionDPStatus.InsString5,SMS_DistributionDPStatus.InsString6,SMS_DistributionDPStatus.InsString7,SMS_DistributionDPStatus.InsString8,SMS_DistributionDPStatus.InsString9,SMS_DistributionDPStatus.IsPeerDP,SMS_DistributionDPStatus.LastStatusID,SMS_DistributionDPStatus.LastUpdateDate,SMS_DistributionDPStatus.MessageCategory,SMS_DistributionDPStatus.MessageFullID,SMS_DistributionDPStatus.MessageID,SMS_DistributionDPStatus.MessageSeverity,SMS_DistributionDPStatus.MessageState,SMS_DistributionDPStatus.NALPath,SMS_DistributionDPStatus.Name,SMS_DistributionDPStatus.ObjectID,SMS_DistributionDPStatus.ObjectTypeID,SMS_DistributionDPStatus.PackageID,SMS_DistributionDPStatus.ResourceType,SMS_DistributionDPStatus.SiteCode from vSMS_DistributionDPStatus AS SMS_DistributionDPStatus  where (SMS_DistributionDPStatus.PackageID = N'XXX' AND SMS_DistributionDPStatus.MessageCategory = N'31')

-- In Progress --

-- WQL
SELECT * FROM SMS_DistributionStatus WHERE PackageID = 'XXX' AND Type = 2
-- SQL
select  all SMS_DistributionStatus.Assets,SMS_DistributionStatus.LastUpdateDate,SMS_DistributionStatus.MessageCategory,SMS_DistributionStatus.ObjectID,SMS_DistributionStatus.ObjectTypeID,SMS_DistributionStatus.PackageID,SMS_DistributionStatus.Type from vSMS_DistributionStatus AS SMS_DistributionStatus  where (SMS_DistributionStatus.PackageID = N'XXX' AND SMS_DistributionStatus.Type = 2)

-- Error --
-- WQL
SELECT * FROM SMS_DistributionStatus WHERE PackageID = 'XXX' AND Type = 4
-- SQL
select  all SMS_DistributionStatus.Assets,SMS_DistributionStatus.LastUpdateDate,SMS_DistributionStatus.MessageCategory,SMS_DistributionStatus.ObjectID,SMS_DistributionStatus.ObjectTypeID,SMS_DistributionStatus.PackageID,SMS_DistributionStatus.Type from vSMS_DistributionStatus AS SMS_DistributionStatus  where (SMS_DistributionStatus.PackageID = N'XXX' AND SMS_DistributionStatus.Type = 4)



Query 23 : Content View Queries (SCCM 2012)
-- Content View All Packages --

-- WQL
SELECT * FROM SMS_ObjectContentInfo
-- SQL 
select  all SMS_ObjectContentInfo.DateCreated,SMS_ObjectContentInfo.Description,SMS_ObjectContentInfo.FeatureType,SMS_ObjectContentInfo.LastUpdateDate,SMS_ObjectContentInfo.NumberErrors,SMS_ObjectContentInfo.NumberInProgress,SMS_ObjectContentInfo.NumberInstalled,SMS_ObjectContentInfo.NumberUnknown,SMS_ObjectContentInfo.ObjectID,SMS_ObjectContentInfo.ObjectType,SMS_ObjectContentInfo.ObjectTypeID,SMS_ObjectContentInfo.PackageID,SMS_ObjectContentInfo.SoftwareName,SMS_ObjectContentInfo.SourceSite,SMS_ObjectContentInfo.SourceSize,SMS_ObjectContentInfo.SourceVersion,SMS_ObjectContentInfo.Targeted from fn_ListObjectContentInfo(1033) AS SMS_ObjectContentInfo

-- Content View Specific Package --
 
-- Success (Installed) --

-- WQL
SELECT * FROM SMS_DistributionDPStatus WHERE PackageID = 'XXX' AND MessageCategory = '31'
-- SQL
select  all SMS_DistributionDPStatus.GroupCount,SMS_DistributionDPStatus.ID,SMS_DistributionDPStatus.InsString1,SMS_DistributionDPStatus.InsString10,SMS_DistributionDPStatus.InsString2,SMS_DistributionDPStatus.InsString3,SMS_DistributionDPStatus.InsString4,SMS_DistributionDPStatus.InsString5,SMS_DistributionDPStatus.InsString6,SMS_DistributionDPStatus.InsString7,SMS_DistributionDPStatus.InsString8,SMS_DistributionDPStatus.InsString9,SMS_DistributionDPStatus.IsPeerDP,SMS_DistributionDPStatus.LastStatusID,SMS_DistributionDPStatus.LastUpdateDate,SMS_DistributionDPStatus.MessageCategory,SMS_DistributionDPStatus.MessageFullID,SMS_DistributionDPStatus.MessageID,SMS_DistributionDPStatus.MessageSeverity,SMS_DistributionDPStatus.MessageState,SMS_DistributionDPStatus.NALPath,SMS_DistributionDPStatus.Name,SMS_DistributionDPStatus.ObjectID,SMS_DistributionDPStatus.ObjectTypeID,SMS_DistributionDPStatus.PackageID,SMS_DistributionDPStatus.ResourceType,SMS_DistributionDPStatus.SiteCode from vSMS_DistributionDPStatus AS SMS_DistributionDPStatus  where (SMS_DistributionDPStatus.PackageID = N'XXX' AND SMS_DistributionDPStatus.MessageCategory = N'31')

-- In Progress --

-- WQL
SELECT * FROM SMS_DistributionStatus WHERE PackageID = 'XXX' AND Type = 2
-- SQL
select  all SMS_DistributionStatus.Assets,SMS_DistributionStatus.LastUpdateDate,SMS_DistributionStatus.MessageCategory,SMS_DistributionStatus.ObjectID,SMS_DistributionStatus.ObjectTypeID,SMS_DistributionStatus.PackageID,SMS_DistributionStatus.Type from vSMS_DistributionStatus AS SMS_DistributionStatus  where (SMS_DistributionStatus.PackageID = N'XXX' AND SMS_DistributionStatus.Type = 2)

-- Error --
-- WQL
SELECT * FROM SMS_DistributionStatus WHERE PackageID = 'XXX' AND Type = 4
-- SQL
select  all SMS_DistributionStatus.Assets,SMS_DistributionStatus.LastUpdateDate,SMS_DistributionStatus.MessageCategory,SMS_DistributionStatus.ObjectID,SMS_DistributionStatus.ObjectTypeID,SMS_DistributionStatus.PackageID,SMS_DistributionStatus.Type from vSMS_DistributionStatus AS SMS_DistributionStatus  where (SMS_DistributionStatus.PackageID = N'XXX' AND SMS_DistributionStatus.Type = 4)

-- Unknown --
-- WQL
SELECT * FROM SMS_DistributionStatus WHERE PackageID = 'XXX' AND Type = 5
-- SQL
select  all SMS_DistributionStatus.Assets,SMS_DistributionStatus.LastUpdateDate,SMS_DistributionStatus.MessageCategory,SMS_DistributionStatus.ObjectID,SMS_DistributionStatus.ObjectTypeID,SMS_DistributionStatus.PackageID,SMS_DistributionStatus.Type from vSMS_DistributionStatus AS SMS_DistributionStatus  where (SMS_DistributionStatus.PackageID = N'XXX' AND SMS_DistributionStatus.Type = 5)

--- Total Analysis --

--- SQL
SELECT     v_Package.Name AS Package_Name, 
     SMS_DistributionDPStatus.PackageID,
     'Package Type (Text)' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End, 
     SMS_DistributionDPStatus.Name AS DP_Name, 
           CASE 
    WHEN MessageCategory = 31 THEN 'Success' 
    WHEN MessageCategory = 76 THEN 'In Progress' 
    WHEN MessageCategory = 13 THEN 'Failed to distribute content'
    WHEN MessageCategory = 70 THEN 'Failed to update package'
    WHEN MessageCategory = 78 THEN 'Retrying package installation'
    WHEN MessageCategory = 51 THEN 'Waiting to install package'
    WHEN MessageCategory = 1  THEN 'Waiting for content'
    WHEN MessageCategory = 77 THEN 'Waiting for prestage content'
    WHEN MessageCategory = 62 THEN 'Content hash has been sucessfully verified'
    WHEN MessageCategory = 7  THEN 'The source folder for content does not exist'
    WHEN MessageCategory = 72 THEN 'Content is being redistributed to distribution point'
    ELSE 'Unknown' 
     END 
     AS 'Status', 
     SMS_DistributionDPStatus.MessageCategory, 
           SMS_DistributionDPStatus.ObjectTypeID, 
           SMS_DistributionDPStatus.LastUpdateDate, 
           SMS_DistributionDPStatus.InsString3, 
           v_Package.PkgSourcePath, 
           v_Package.PackageType
FROM         vSMS_DistributionDPStatus AS SMS_DistributionDPStatus INNER JOIN
                      v_Package ON SMS_DistributionDPStatus.PackageID = v_Package.PackageID
           where SMS_DistributionDPStatus.MessageCategory <> 31
           and SMS_DistributionDPStatus.MessageCategory <> 76
           order by SMS_DistributionDPStatus.PackageID


Query 24 : Distribution Status (SCCM 2012)
SELECT     v_Package.Name AS Package_Name, 
     SMS_DistributionDPStatus.PackageID,
     'Package Type (Text)' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End, 
     SMS_DistributionDPStatus.Name AS DP_Name,
     CASE
       WHEN MessageState = 1 THEN 'Success'
    WHEN MessageState = 2 THEN 'In Progress'
    WHEN MessageState = 4 THEN 'Error'
   Else
    'Unknown'
   End as 'Content_Status',
           CASE 
    WHEN MessageCategory = 31 THEN 'Success' 
    WHEN MessageCategory = 76 THEN 'In Progress' 
    WHEN MessageCategory = 13 THEN 'Failed to distribute content'
    WHEN MessageCategory = 70 THEN 'Failed to update package'
    WHEN MessageCategory = 78 THEN 'Failed to connect to remote distribution point'
    WHEN MessageCategory = 51 THEN 'Content transfer manager was instructed to send content to the distribution point'
    WHEN MessageCategory = 37 THEN 'Content transfer manager was instructed to send content to the distribution point'
    WHEN MessageCategory = 1  THEN 'Waiting for prestage content'
    WHEN MessageCategory = 77 THEN 'Waiting for prestage content'
    WHEN MessageCategory = 62 THEN 'Content hash has been sucessfully verified'
    WHEN MessageCategory = 7  THEN 'The source folder for content does not exist'
    WHEN MessageCategory = 72 THEN 'Content is being redistributed to distribution point'
    WHEN MessageCategory = 4 THEN 'Content was successfully refreshed'
    WHEN MessageCategory = 998 THEN 'Content transfer manager was instructed to send content to the distribution point'
    WHEN MessageCategory = 165 THEN 'Content was distributed to distribution point'
    WHEN MessageCategory = 61 THEN 'Failed to validate content hash'
    WHEN MessageCategory = 24 THEN 'Failed to connect to distribution point'
    ELSE 'Unknown' 
     END 
     AS 'Detailed_Status', 
     SMS_DistributionDPStatus.MessageState,
     SMS_DistributionDPStatus.MessageCategory, 
           SMS_DistributionDPStatus.ObjectTypeID, 
           SMS_DistributionDPStatus.LastUpdateDate, 
           SMS_DistributionDPStatus.InsString3, 
           v_Package.PkgSourcePath, 
           v_Package.PackageType
FROM         vSMS_DistributionDPStatus AS SMS_DistributionDPStatus INNER JOIN
                      v_Package ON SMS_DistributionDPStatus.PackageID = v_Package.PackageID   
           order by SMS_DistributionDPStatus.PackageID



Query 24a : Distribution Status - with Percentage Complete (SCCM 2012)
select
 pkg.PackageID,
 pkg.Name as 'Package Name',
 'Package Type' =
  Case
   when pkg.PackageType = 0 Then 'Software Distribution Package'
   when pkg.PackageType = 3 Then 'Driver Package'
   when pkg.PackageType = 4 Then 'Task Sequence Package'
   when pkg.PackageType = 5 Then 'Software Update Package'
   when pkg.PackageType = 6 Then 'Device Setting Package'
   when pkg.PackageType = 7 Then 'Virtual Package'
   when pkg.PackageType = 8 Then 'Application'
   when pkg.PackageType = 257 Then 'Image Package'
   when pkg.PackageType = 258 Then 'Boot Image Package'
   when pkg.PackageType = 259 Then 'Operating System Install Package'
  Else
   'Unknown'
  End, 
 SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', 
    dp.ServerNALPath) + 2, CHARINDEX('"]',   dp.ServerNALPath) - CHARINDEX('\\', 
    dp.ServerNALPath) - 3 ) AS 'Distribution Point',
 dp.SiteCode,
 dp.LastRefreshTime,
 stat.SourceVersion,
 stat.LastCopied,
 stat.SummaryDate,
 (select top 1 msg.InsString3
 from v_StatMsgWithInsStrings msg
 join v_StatMsgModuleNames modNames on msg.ModuleName = modNames.ModuleName
 join v_StatMsgAttributes attpkg on msg.RecordID=attpkg.RecordID and msg.Time=attpkg.AttributeTime
 join v_StatMsgAttributes attdp on msg.RecordID=attdp.RecordID and msg.Time=attdp.AttributeTime
 where attpkg.AttributeValue =pkg.PackageID and msg.MessageID='8204'
 and msg.InsString2 =  SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', 
    dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', 
    dp.ServerNALPath) - 3 ) 
 order by 
    msg.Time desc) as '% Completed',
    stat.InstallStatus
 from v_Package pkg
 join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
 join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath
 and 
 dp.PackageID=stat.PackageID
 where 
 stat.State!=0
 order by 
 pkg.Name, dp.SiteCode


Query 24b : Distribution Status - Simple (SCCM 2012) - V3
SELECT        v_Package.PackageID, 
              v_Package.Name,
     SUBSTRING(DistributionPoint, CHARINDEX('\\', 
               DistributionPoint) + 2, CHARINDEX('"]', DistributionPoint) - CHARINDEX('\\', 
               DistributionPoint) - 3 ) AS 'Distribution Point',
     'State' =
     case
     when v_ContentDistribution.State = 0 then 'Success'
     when v_ContentDistribution.State = 1 then 'In Progress'
     when v_ContentDistribution.State = 2 then 'Error'
     else
     'Unknown'
     end,
      'Package Type' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End,

      v_Package.ActionInProgress
FROM            v_ContentDistribution INNER JOIN
                         v_Package ON v_ContentDistribution.PkgID = v_Package.PackageID
order by v_Package.Name


Query 25 : Prestage settings (SCCM 2012)
 -- Prestage Settings for DPs

Select ServerName,PreStagingAllowed From v_DistributionPointInfo

 
-- Prestage Settings

select PackageID,Name,PkgFlags,
 'Package Type (Text)' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End,
        CASE
   When PkgFlags & 32 = 32 Then 'Automatically download content'
   When PkgFlags & 16 = 16 Then 'Download only content changes'
   When PkgFlags & 16777216 = 16777216 Then 'Manually copy content'
  Else
   'Unknown'
        END as 'Prestage Settings'
from v_Package


Query 25 : Prestage settings (SCCM 2012)
 -- Prestage Settings for DPs

Select ServerName,PreStagingAllowed From v_DistributionPointInfo

 
-- Prestage Settings

select PackageID,Name,PkgFlags,
 'Package Type (Text)' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End,
        CASE
   When PkgFlags & 32 = 32 Then 'Automatically download content'
   When PkgFlags & 16 = 16 Then 'Download only content changes'
   When PkgFlags & 16777216 = 16777216 Then 'Manually copy content'
  Else
   'Unknown'
        END as 'Prestage Settings'
from v_Package


Query 26 : Distribution Status - Multi-Purpose (SCCM 2012)
declare @current_time as nvarchar(20)

set @current_time = convert(varchar, getdate(), 3) + ' ' + convert(varchar, getdate(), 24)
Set @UserSIDs = 'Disabled'

declare @report_type as int

set @report_type = 1 -- Totals Statistics
set @report_type = 2 -- Distribution Jobs List
set @report_type = 3 -- Distribution Jobs - Percentage complete - V1
set @report_type = 4 -- Distribution Jobs - Percentage complete - V2

set @report_type = 4

/* ---- Totals Statistics -------------------------------------------------

This is a report designed to give the summery for different distribution job
states. e.g. How many packages are 'Waiting to Install' on distribution points.

Report Type: 1

   ---------------------------------------------------------------------- */

if @report_type = 1 
 Begin
  print 'Totals Statistics'
  select State,COUNT(*)as Number,
   fn_rbac_PackageStatusDistPointsSumm.InstallStatus,
   @current_time as 'Timestamp'   
   from 
   fn_rbac_PackageStatusDistPointsSumm(@UserSIDs) Group By State,InstallStatus
   order by State
 return
 end 

/* ---- Distribution Jobs List -------------------------------------------------

   This lists all the various distribution jobs targeted to distribution 
   points and there current states.

Report Type: 2

-------------------------------------------------------------------------- */

if @report_type = 2
 Begin
  print 'Distribution Jobs..'

  select 
  Left(SubString(fn_rbac_PackageStatusDistPointsSumm.ServerNALPath, CHARINDEX('\\', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath)+2,(CHARINDEX('"]', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath) - CHARINDEX('\\', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath))-3),
  CHARINDEX('.',SubString(fn_rbac_PackageStatusDistPointsSumm.ServerNALPath, CHARINDEX('\\', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath)+2,(CHARINDEX('"]', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath) - CHARINDEX('\\', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath))-3))-1) AS Server_Name,  
  fn_rbac_PackageStatusDistPointsSumm.PackageID,
  v_Package.Name as 'Package Name',  
  fn_rbac_PackageStatusDistPointsSumm.InstallStatus,
  fn_rbac_PackageStatusDistPointsSumm.State,
   'Package Type (Text)' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End,
   @current_time as 'Current Time'
   from 
    fn_rbac_PackageStatusDistPointsSumm(@UserSIDs) INNER JOIN                     
                      v_Package ON fn_rbac_PackageStatusDistPointsSumm.PackageID = v_Package.PackageID
       where fn_rbac_PackageStatusDistPointsSumm.State <> 0
  return
 end     

/* --- Distribution Jobs - Percentage complete V1 ------------------------------------

This lists the current packages that are in progress and displays a percentage of how
much has been sent.

Report Type: 3

----------------------------------------------------------------------------------- */

if @report_type = 3
 begin
  print 'Distribution Jobs - Percentage complete V1..'
  select 
  Left(SubString(fn_rbac_PackageStatusDistPointsSumm.ServerNALPath, CHARINDEX('\\', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath)+2,(CHARINDEX('"]', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath) - CHARINDEX('\\', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath))-3),
  CHARINDEX('.',SubString(fn_rbac_PackageStatusDistPointsSumm.ServerNALPath, CHARINDEX('\\', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath)+2,(CHARINDEX('"]', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath) - CHARINDEX('\\', fn_rbac_PackageStatusDistPointsSumm.ServerNALPath))-3))-1) AS Server_Name,  
  fn_rbac_PackageStatusDistPointsSumm.PackageID,
   v_Package.Name as 'Package Name',
   'Package Type (Text)' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End,
    vSMS_DistributionDPStatus.InsString3 as 'Percent Complete',
    @current_time as 'Current Time'
    from fn_rbac_PackageStatusDistPointsSumm(@UserSIDs) INNER JOIN
     vSMS_DistributionDPStatus ON 
                    fn_rbac_PackageStatusDistPointsSumm.PackageID = vSMS_DistributionDPStatus.ObjectID 
                    INNER JOIN
                    v_Package ON fn_rbac_PackageStatusDistPointsSumm.PackageID = v_Package.PackageID
    where fn_rbac_PackageStatusDistPointsSumm.State = 1 and 
     vSMS_DistributionDPStatus.MessageCategory = 76 
  return
 end

/* --- Distribution Jobs - Percentage complete V2 ------------------------------------

This lists the current packages that are in progress and displays a percentage of how
much has been sent. This is the same type of report as V1 but using a different 
method to get the results.

----------------------------------------------------------------------------------- */

if @report_type = 4
 begin
  print 'Distribution Jobs - Percentage complete V2..'
  select SubString(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath)+2,(CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath))-3) AS ServerName, pkg.PackageID,
   pkg.Manufacturer, pkg.Name, pkg.Version, stat.SourceVersion,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), stat.SummaryDate) AS LastRefreshTime,
   (select top 1 msg.InsString3
   from [dbo].[v_StatMsgWithInsStrings] msg
   JOIN [dbo].[v_StatMsgModuleNames] modNames on msg.ModuleName = modNames.ModuleName
   JOIN [dbo].[v_StatMsgAttributes] attpkg on msg.RecordID=attpkg.RecordID and msg.Time=attpkg.AttributeTime
   JOIN [dbo].[v_StatMsgAttributes] attdp on msg.RecordID=attdp.RecordID and msg.Time=attdp.AttributeTime
   where attpkg.AttributeValue =pkg.PackageID and msg.MessageID = '8204' and msg.InsString2 =SubString(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath)+2,(CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath))-3)
   order by msg.Time desc) AS '% Completed',
   stat.InstallStatus
  from v_Package pkg
   JOIN v_DistributionPoint dp ON pkg.PackageID=dp.PackageID
   JOIN v_PackageStatusDistPointsSumm stat ON dp.ServerNALPath=stat.ServerNALPath
   AND dp.PackageID=stat.PackageID
   where stat.State!=0
   order by ServerName
 end 


Query 27 : Prestage Settings (SCCM 2012)
 -- Prestage Settings for DPs
 Select ServerName,PreStagingAllowed From v_DistributionPointInfo
 
-- Prestage Settings
 select PackageID,Name,PkgFlags,
 'Package Type (Text)' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End,
        CASE
   When PkgFlags & 32 = 32 Then 'Automatically download content'
   When PkgFlags & 16 = 16 Then 'Download only content changes'
   When PkgFlags & 16777216 = 16777216 Then 'Manually copy content'
  Else
   'Unknown'
        END as 'Prestage Settings'
from v_Package


Query 28 : Fix in SCCM DB when packages wont distribute
# Use the following carefully!

delete from PkgServers where PkgID = 'XXX' and NALPath like '%XXX%'


Query 29 : List all tables in SCCM
sp_MSforeachtable @command1="print '?'"


Query 30 : Collection Rules
select * from Collection_Rules_SQL where CollectionID = 'XXX'
select * from v_CollectionRuleQuery where CollectionID = 'XXX'
select * from v_Collection where CollectionID = 'XXX

select * from Collection_Rules_SQL where CollectionID = XXX


Query 31 : List AD Sites from SCCM
select distinct AD_Site_Name0 from vSMS_R_System order by AD_Site_Name0


Query 32 : Check SQL Fragmentation
-- URL
-- ConfigMgr 2012 Index Optimization
-- http://stevethompsonmvp.wordpress.com/2014/05/
-- http://blogs.technet.com/b/smartinez/archive/2014/03/28/talking-database-in-configmgr.aspx
-- http://myitforum.com/myitforumwp/2013/04/19/how-to-determine-if-the-configmgr-rebuild-indexes-site-maintenance-task-is-running/
-- http://www.blogmynog.com/2013/05/21/sccm-2012-poor-console-performance-in-software-updates/

-- Use ALTER INDEX REORGANIZE option for avg_fragmentation_in_percent between 5% and 30%  
-- Use ALTER INDEX REBUILD option for avg_fragmentation_in_percent > 30%. 

-- Reorganize and Rebuild Indexes
-- http://msdn.microsoft.com/en-us/library/ms189858.aspx


-- SCCM Log file: SMSDBMON.LOG (Message ID: 2408)

declare @current_time as nvarchar(20)

set @current_time = convert(varchar, getdate(), 3) + ' ' + convert(varchar, getdate(), 24)

SELECT @current_time as Timestamp,
DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 1500
ORDER BY OBJECT_NAME(ps.OBJECT_ID), avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

----------------

declare @current_time as nvarchar(20)

set @current_time = convert(varchar, getdate(), 3) + ' ' + convert(varchar, getdate(), 24)

SELECT @current_time as Timestamp,
DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);


-----------------


SELECT a.index_id, name, avg_fragmentation_in_percent FROM
sys.dm_db_index_physical_stats (NULL,NULL, NULL, NULL, NULL) AS a JOIN sys.indexes
AS b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE
avg_fragmentation_in_percent > 30
 
GO

------------------------------------

-- Check when task last run.

select * from  SQLTaskStatus where TaskName = 'Rebuild Indexes'

-- http://anoopcnair.com/2010/12/06/reporting-of-maintenance-tasks-status-of-sccm-primary-server/

select *, floor(DATEDIFF(ss,laststarttime,lastcompletiontime)/3600) as Hours, floor(DATEDIFF(ss,laststarttime,lastcompletiontime)/60)- floor(DATEDIFF(ss,laststarttime,lastcompletiontime)/3600)*60 as Minutes, floor(DATEDIFF(ss,laststarttime,lastcompletiontime))- floor(DATEDIFF(ss,laststarttime,lastcompletiontime)/60)*60 as TotalSeconds from SQLTaskStatus where TaskName = 'Rebuild Indexes'

-----------------------------------------

-- SCCM Index Defrag

-- Below is what SCCM uses to work out what to re-index:

select distinct sch.name + '.' + OBJECT_NAME(stat.object_id), ind.name, convert(int,stat.avg_fragmentation_in_percent) from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') stat join sys.indexes ind on stat.object_id=ind.object_id and stat.index_id=ind.index_id join sys.objects  obj on obj.object_id=stat.object_id join sys.schemas sch on obj.schema_id=sch.schema_id   where ind.name is not null and stat.avg_fragmentation_in_percent > 10.0 and ind.type > 0 order by convert(int,stat.avg_fragmentation_in_percent) desc

-- This is what SCCM does for each index.

alter index CI_CategoryInstances_PK on dbo.CI_CategoryInstances REBUILD -- Sample

--------------------------

-- Index Fragmentation Report - V1

declare @current_time as nvarchar(20)

set @current_time = convert(varchar, getdate(), 3) + ' ' + convert(varchar, getdate(), 24)

select distinct 
       @current_time as Timestamp,
       sch.name + '.' + OBJECT_NAME(stat.object_id) as 'Table_Name', 
       ind.name as 'Index_Name', 
       convert(int,stat.avg_fragmentation_in_percent) as 'Average Fragementation %',
'Alter index ' + ind.name + ' on '+ sch.name + '.' + OBJECT_NAME(stat.object_id) + ' REBUILD' as 'Reindex Command'
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') stat join sys.indexes ind on 
     stat.object_id=ind.object_id and stat.index_id=ind.index_id join sys.objects  obj on 
     obj.object_id=stat.object_id join sys.schemas sch on 
     obj.schema_id=sch.schema_id   where ind.name is not null and 
     ind.type > 0 order by convert(int,stat.avg_fragmentation_in_percent) desc

-----

-- Index Fragmentation Report - V2


declare @current_time as nvarchar(20)

set @current_time = convert(varchar, getdate(), 3) + ' ' + convert(varchar, getdate(), 24)

SELECT @current_time as Timestamp,
DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);



Query 33 : Re-Index database
-- Force Reindex task --
-- Note: Run at own risk --

Update SQLTaskStatus set TaskName = N'Rebuild Indexes',
                         TaskType = 1,
                         LastStartTime = N'01/01/1980 00:00:00',
                         LastCompletionTime = N'01/01/1980 00:00:00',
                         RunNow = 0

-- After this change the Reindex schedule

-- Reindex outside of SCCM - Stop SCCM services first --

USE CM_XXX
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable "Print 'Reindexing '+'?' DBCC DBREINDEX ('?')"


Query 34 : Return OS Info
SELECT     v_R_System.ResourceID, 
     v_R_System.Name0,
     v_R_System.Netbios_Name0, 
     v_GS_OPERATING_SYSTEM.Caption0, 
     v_GS_OPERATING_SYSTEM.CSDVersion0
FROM       v_R_System INNER JOIN
           v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE      (v_R_System.Netbios_Name0 like 'XX%')


Query 35 : Boundary Info
SELECT        vSMS_Boundary.BoundaryID, 
              vSMS_Boundary.DisplayName, 
        vSMS_Boundary.Value,
              v_BoundarySiteSystems.SiteSystemName,
              'Boundary Type' =
       Case
        when vSMS_Boundary.BoundaryType = 0 Then 'IP Subnet'
        when vSMS_Boundary.BoundaryType = 1 Then 'Active Directory Site'
        when vSMS_Boundary.BoundaryType = 2 Then 'IPV6 Prefix'
        when vSMS_Boundary.BoundaryType = 3 Then 'IP Range'
        end,
                          vSMS_Boundary.GroupCount
FROM            vSMS_Boundary INNER JOIN
                         v_BoundarySiteSystems ON vSMS_Boundary.BoundaryID = v_BoundarySiteSystems.BoundaryID
ORDER BY vSMS_Boundary.DisplayName



Query 36 : Distribution Points
SELECT DISTINCT ServerName FROM fn_rbac_DistributionPointInfo('Disabled') order by ServerName


Query 37a : Deployment Types
SELECT     fn_DeploymentSummary_1.PackageID, 
           fn_DeploymentSummary_1.SoftwareName, 
     fn_DeploymentSummary_1.CollectionID, 
           fn_DeploymentSummary_1.CollectionName,           
     'Deployment Type' =
        case
                  when fn_DeploymentSummary_1.DeploymentIntent = 1 then 'Required'
                  when fn_DeploymentSummary_1.DeploymentIntent = 2 then 'Available'
        end,
          'Package Type' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End,
     fn_DeploymentSummary_1.AssignmentID, fn_DeploymentSummary_1.CI_ID, fn_DeploymentSummary_1.DeploymentID 
FROM         dbo.fn_DeploymentSummary(1033) AS fn_DeploymentSummary_1 INNER JOIN
                      v_Package ON fn_DeploymentSummary_1.PackageID = v_Package.PackageID


Query 37b : Deployment Types
SELECT        v_Package.PackageID,
     v_Package.Name as 'Package Name',
              'Package Type' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
     Else
     'Unknown'
     End,      
     v_DeploymentSummary.SoftwareName, 
     case
     when v_DeploymentSummary.DeploymentIntent = 1 then 'Required'
     when v_DeploymentSummary.DeploymentIntent = 2 then 'Available'
     else
     'Unknown'
     end as 'Deployment Intent',
     v_Collection.CollectionID, 
     v_Collection.Name AS 'Collection Name',
      v_Collection.MemberCount, 
      v_DeploymentSummary.AssignmentID, 
               v_DeploymentSummary.ModelID
FROM            v_Package left JOIN
                         v_DeploymentSummary ON v_Package.PackageID = v_DeploymentSummary.PackageID INNER JOIN
                         v_Collection ON v_DeploymentSummary.CollectionID = v_Collection.CollectionID
order by v_Package.Name


Query 38 : Deployment Types - Task Sequences Only
SELECT     fn_DeploymentSummary_1.PackageID, 
           fn_DeploymentSummary_1.SoftwareName, 
     fn_DeploymentSummary_1.CollectionID, 
           fn_DeploymentSummary_1.CollectionName,           
     'Deployment Type' =
        case
                  when fn_DeploymentSummary_1.DeploymentIntent = 1 then 'Required'
                  when fn_DeploymentSummary_1.DeploymentIntent = 2 then 'Available'
        end,
          'Package Type' =
    Case
     when v_Package.PackageType = 0 Then 'Software Distribution Package'
     when v_Package.PackageType = 3 Then 'Driver Package'
     when v_Package.PackageType = 4 Then 'Task Sequence Package'
     when v_Package.PackageType = 5 Then 'Software Update Package'
     when v_Package.PackageType = 6 Then 'Device Setting Package'
     when v_Package.PackageType = 7 Then 'Virtual Package'
     when v_Package.PackageType = 8 Then 'Application'
     when v_Package.PackageType = 257 Then 'Image Package'
     when v_Package.PackageType = 258 Then 'Boot Image Package'
     when v_Package.PackageType = 259 Then 'Operating System Install Package'
    Else
     'Unknown'
    End,
     fn_DeploymentSummary_1.AssignmentID, fn_DeploymentSummary_1.CI_ID, fn_DeploymentSummary_1.DeploymentID 
FROM         dbo.fn_DeploymentSummary(1033) AS fn_DeploymentSummary_1 INNER JOIN
                      v_Package ON fn_DeploymentSummary_1.PackageID = v_Package.PackageID
where v_Package.PackageType = 4


Query 38a : All Package References for all task sequences
SELECT        fn_DeploymentSummary_1.PackageID, 
     fn_DeploymentSummary_1.SoftwareName, 
     fn_DeploymentSummary_1.CollectionID, 
              fn_DeploymentSummary_1.CollectionName, 
              CASE 
     WHEN fn_DeploymentSummary_1.DeploymentIntent = 1 THEN 'Required' 
     WHEN fn_DeploymentSummary_1.DeploymentIntent = 2 THEN 'Available' 
     END AS 'Deployment Type',
              CASE 
     WHEN v_Package.PackageType = 0 THEN 'Software Distribution Package' 
     WHEN v_Package.PackageType = 3 THEN 'Driver Package' 
     WHEN v_Package.PackageType = 4 THEN 'Task Sequence Package' 
     WHEN v_Package.PackageType = 5 THEN 'Software Update Package' 
     WHEN v_Package.PackageType = 6 THEN 'Device Setting Package'
                    WHEN v_Package.PackageType = 7 THEN 'Virtual Package' 
     WHEN v_Package.PackageType = 8 THEN 'Application' 
     WHEN v_Package.PackageType = 257 THEN 'Image Package'
                    WHEN v_Package.PackageType = 258 THEN 'Boot Image Package' 
     WHEN v_Package.PackageType = 259 THEN 'Operating System Install Package' 
     ELSE 
    'Unknown'
              END AS 'Package Type', 
     fn_DeploymentSummary_1.DeploymentID, 
              v_Package_1.PackageID AS Reference_PackageID, 
     v_Package_1.Name AS Reference_PackageName, 
     v_Package_1.PkgSourcePath AS Reference_PkgSourcePath,               
              CASE 
     WHEN v_Package_1.PackageType = 0 THEN 'Software Distribution Package' 
     WHEN v_Package_1.PackageType = 3 THEN 'Driver Package' 
     WHEN v_Package_1.PackageType = 4 THEN 'Task Sequence Package' 
     WHEN v_Package_1.PackageType = 5 THEN 'Software Update Package' 
     WHEN v_Package_1.PackageType = 6 THEN 'Device Setting Package'
                    WHEN v_Package_1.PackageType = 7 THEN 'Virtual Package' 
     WHEN v_Package_1.PackageType = 8 THEN 'Application' 
     WHEN v_Package_1.PackageType = 257 THEN 'Image Package'
                    WHEN v_Package_1.PackageType = 258 THEN 'Boot Image Package' 
     WHEN v_Package_1.PackageType = 259 THEN 'Operating System Install Package' 
     ELSE 
    'Unknown'
              END AS 'Reference Package Type',
     v_Package_1.PackageType AS Reference_PackageType
FROM          dbo.fn_DeploymentSummary(1033) AS fn_DeploymentSummary_1 INNER JOIN
                         v_Package ON fn_DeploymentSummary_1.PackageID = v_Package.PackageID INNER JOIN
                         v_TaskSequencePackageReferences ON fn_DeploymentSummary_1.PackageID = v_TaskSequencePackageReferences.PackageID INNER JOIN
                         v_Package AS v_Package_1 ON v_TaskSequencePackageReferences.RefPackageID = v_Package_1.PackageID
WHERE        (v_Package.PackageType = 4)


Query 38b : Task Sequences Deployments V2
SELECT        fn_DeploymentSummary_1.PackageID, 
     fn_DeploymentSummary_1.SoftwareName as Name, 
     fn_DeploymentSummary_1.CollectionID, 
              fn_DeploymentSummary_1.CollectionName, 
              CASE 
     WHEN fn_DeploymentSummary_1.DeploymentIntent = 1 THEN 'Required' 
     WHEN fn_DeploymentSummary_1.DeploymentIntent = 2 THEN 'Available' 
     END AS 'Deployment Type',
     fn_DeploymentSummary_1.DeploymentID, 
              v_Package_1.PackageID AS 'Boot Image Package ID', 
     v_Package_1.Name AS 'Boot Image Package Name', 
     v_Package_1.PkgSourcePath AS 'Boot Image PkgSourcePath'
FROM          dbo.fn_DeploymentSummary(1033) AS fn_DeploymentSummary_1 INNER JOIN
                         v_Package ON fn_DeploymentSummary_1.PackageID = v_Package.PackageID INNER JOIN
                         v_TaskSequencePackageReferences ON fn_DeploymentSummary_1.PackageID = v_TaskSequencePackageReferences.PackageID INNER JOIN
                         v_Package AS v_Package_1 ON v_TaskSequencePackageReferences.RefPackageID = v_Package_1.PackageID
WHERE        (v_Package.PackageType = 4 and v_Package_1.PackageType = 258)


Query 39a : Collection Rules
SELECT        v_Collection.CollectionID, v_Collection.Name, v_Collection.Comment, v_Collection.LastChangeTime, v_Collection.EvaluationStartTime, 
                         v_Collection.LastRefreshTime, v_Collection.RefreshType, v_Collection.CollectionType, v_Collection.CurrentStatus, v_Collection.MemberCount, 
                         v_Collection.MemberClassName, v_Collection.LastMemberChangeTime, v_Collection.CollID, Collection_Rules_SQL.QueryKey, Collection_Rules_SQL.WQL, 
                         Collection_Rules_SQL.SQL
FROM            v_Collection INNER JOIN
                         Collection_Rules_SQL ON v_Collection.CollID = Collection_Rules_SQL.CollectionID
WHERE        (v_Collection.CollectionID = 'XXXX')


Query 39b : Collection Rules
SELECT        v_Collection.CollectionID, v_Collection.Name, v_Collection.Comment, v_Collection.LastChangeTime, v_Collection.EvaluationStartTime, 
                         v_Collection.LastRefreshTime, v_Collection.RefreshType, v_Collection.CollectionType, v_Collection.CurrentStatus, v_Collection.MemberCount, 
                         v_Collection.MemberClassName, v_Collection.LastMemberChangeTime, v_Collection.CollID, Collection_Rules_SQL.QueryKey, Collection_Rules_SQL.WQL, 
                         Collection_Rules_SQL.SQL,
                         SUBSTRING(Collection_Rules_SQL.SQL,CHARINDEX('\\',Collection_Rules_SQL.SQL,CHARINDEX('SMS_R_System.SystemGroupName',Collection_Rules_SQL.SQL))+2,LEN(Collection_Rules_SQL.SQL)-CHARINDEX('\\',Collection_Rules_SQL.SQL,CHARINDEX('SMS_R_System.SystemGroupName',Collection_Rules_SQL.SQL))-2) as 'AD Group'
FROM            v_Collection INNER JOIN
                         Collection_Rules_SQL ON v_Collection.CollID = Collection_Rules_SQL.CollectionID
WHERE        (v_Collection.CollectionID = 'XXXX')


Query 40 : Add/Remove Programs by Machine Name
declare @UserSIDs as nvarchar(20)
declare @computername as nvarchar(20)

Set @UserSIDs = 'Disabled'
set @computername = 'XXX'

SELECT        v_R_System.Name0,
       v_R_System.ResourceID,
       fn_rbac_Add_Remove_Programs_1.DisplayName0, 
              fn_rbac_Add_Remove_Programs_1.InstallDate0, 
              fn_rbac_Add_Remove_Programs_1.Publisher0, 
       fn_rbac_Add_Remove_Programs_1.Version0,
       fn_rbac_Add_Remove_Programs_1.ProdID0,
       v_R_System.AD_Site_Name0,  
       vWorkstationStatus.LastHardwareScan,
       v_R_System.Active0,
       v_R_System.Client0,
       v_R_System.Obsolete0,
       v_R_System.Client_Version0, 
       vWorkstationStatus.LastDDR, 
              vWorkstationStatus.LastPolicyRequest, 
       vWorkstationStatus.LastMPServerName
FROM          v_R_System INNER JOIN
              dbo.fn_rbac_Add_Remove_Programs(@UserSIDs) AS fn_rbac_Add_Remove_Programs_1 ON 
              v_R_System.ResourceID = fn_rbac_Add_Remove_Programs_1.ResourceID INNER JOIN
              vWorkstationStatus ON v_R_System.ResourceID = vWorkstationStatus.ResourceID
where         v_R_System.Name0 = @computername
Order by fn_rbac_Add_Remove_Programs_1.DisplayName0


Query 41 : Add/Remove Programs by Machine Name V2
declare @computername as nvarchar(20)
set @computername = 'XXXX'

-- 32 Bit Apps

SELECT        v_R_System.Name0, 
              v_R_System.ResourceID, 
     '32 Bit' as 'Program Type',
     v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 AS 'Display Name', 
              v_GS_ADD_REMOVE_PROGRAMS.InstallDate0 AS 'Install Date', 
     v_GS_ADD_REMOVE_PROGRAMS.Publisher0 AS 'Publisher',
     v_GS_ADD_REMOVE_PROGRAMS.Version0 AS 'Version', 
              v_GS_ADD_REMOVE_PROGRAMS.ProdID0 AS 'Product Code', 
            v_R_System.AD_Site_Name0,  
           v_R_System.Active0,
           v_R_System.Client0,
           v_R_System.Obsolete0,
           v_R_System.Client_Version0,
                  vWorkstationStatus.LastHardwareScan,
     vWorkstationStatus.LastDDR, 
              vWorkstationStatus.LastPolicyRequest, 
           vWorkstationStatus.LastMPServerName
FROM            v_R_System INNER JOIN
                         v_GS_ADD_REMOVE_PROGRAMS ON v_R_System.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
    INNER JOIN
              vWorkstationStatus ON v_R_System.ResourceID = vWorkstationStatus.ResourceID
WHERE        (v_R_System.Name0 = @computername)
UNION

-- 64 Bit Apps

SELECT        v_R_System.Name0, 
              v_R_System.ResourceID, 
     '64 Bit' as 'Program Type',
     v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 AS 'Display Name', 
              v_GS_ADD_REMOVE_PROGRAMS_64.InstallDate0 AS 'Install Date', 
     v_GS_ADD_REMOVE_PROGRAMS_64.Publisher0 AS 'Publisher', 
              v_GS_ADD_REMOVE_PROGRAMS_64.Version0 AS 'Version', 
     v_GS_ADD_REMOVE_PROGRAMS_64.ProdID0 AS 'Product Code',
     v_R_System.AD_Site_Name0,  
           v_R_System.Active0,
           v_R_System.Client0,
           v_R_System.Obsolete0,
           v_R_System.Client_Version0,
                  vWorkstationStatus.LastHardwareScan,
     vWorkstationStatus.LastDDR, 
              vWorkstationStatus.LastPolicyRequest, 
           vWorkstationStatus.LastMPServerName
FROM          v_R_System INNER JOIN
                         v_GS_ADD_REMOVE_PROGRAMS_64 ON v_R_System.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID
       INNER JOIN
              vWorkstationStatus ON v_R_System.ResourceID = vWorkstationStatus.ResourceID
WHERE        (v_R_System.Name0 = @computername)
order by 'Display Name'



Query 42 : Collection Type Analysis
select CollectionID,
       Name,
       CASE
           WHEN RefreshType = 1 THEN 'Manual'
           WHEN RefreshType = 2 THEN 'Schedule'
    WHEN RefreshType = 4 THEN 'Incremental - no schedule'
           WHEN RefreshType = 6 THEN 'Incremental - with schedule'
 Else
  'Unknown'
 End as 'Refresh Type (Text)',
        RefreshType,
        Membercount,
 LastChangeTime,
 LastMemberChangeTime,
 LastRefreshTime
from v_Collection
    Order by Name


Query 43 : Collection Type Count
select CASE
           WHEN RefreshType = 1 THEN 'Manual'
           WHEN RefreshType = 2 THEN 'Schedule'
        WHEN RefreshType = 4 THEN 'Incremental - no schedule'
           WHEN RefreshType = 6 THEN 'Incremental - with schedule'
     Else
     'Unknown'
 End as 'Refresh Type (Text)',
 RefreshType,
 count(*) as Count from v_Collection group by RefreshType
 order by RefreshType


Query 44 : AD Group Membership
declare @computername as nvarchar(20)
set @computername = 'XXX'

SELECT        v_R_System.Name0, 
              v_RA_System_SystemGroupName.System_Group_Name0
FROM          v_R_System INNER JOIN
              v_RA_System_SystemGroupName ON v_R_System.ResourceID = v_RA_System_SystemGroupName.ResourceID
     where Name0 = @computername
     order by v_RA_System_SystemGroupName.System_Group_Name0


Query 45 : Add/Remove Programs by Specific Software
declare @Software_Name as nvarchar(80)
set @Software_Name = 'XXX'

SELECT        v_R_System.Name0, fn_rbac_Add_Remove_Programs_1.DisplayName0, fn_rbac_Add_Remove_Programs_1.InstallDate0 AS Expr1, 
                         fn_rbac_Add_Remove_Programs_1.Publisher0, fn_rbac_Add_Remove_Programs_1.Version0, v_GS_OPERATING_SYSTEM.Caption0, 
                         v_GS_OPERATING_SYSTEM.CSDVersion0, v_GS_OPERATING_SYSTEM.Description0, v_GS_OPERATING_SYSTEM.InstallDate0, 
                         v_GS_OPERATING_SYSTEM.LastBootUpTime0, vWorkstationStatus.LastPolicyRequest, vWorkstationStatus.LastHardwareScan, vWorkstationStatus.ClientVersion, 
                         v_R_System.Client0, v_R_System.Active0, v_R_System.Client_Version0, v_R_System.Obsolete0
FROM            dbo.fn_rbac_Add_Remove_Programs('disabled') AS fn_rbac_Add_Remove_Programs_1 INNER JOIN
                         v_R_System ON fn_rbac_Add_Remove_Programs_1.ResourceID = v_R_System.ResourceID INNER JOIN
                         v_GS_OPERATING_SYSTEM ON fn_rbac_Add_Remove_Programs_1.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
                         vWorkstationStatus ON fn_rbac_Add_Remove_Programs_1.ResourceID = vWorkstationStatus.ResourceID
WHERE        (fn_rbac_Add_Remove_Programs_1.DisplayName0 = @Software_Name)
ORDER BY vWorkstationStatus.LastHardwareScan DESC



Query 46 : Last Inventory Date
declare @Computer as nvarchar(20)
set @Computer  = 'XXX'

SELECT        v_R_System.ResourceID, v_R_System.Name0, v_R_System.Creation_Date0, v_GS_WORKSTATION_STATUS.LastHWScan, 
                         v_GS_LastSoftwareScan.LastScanDate AS LastSWScan, v_R_System.Client0, v_R_System.Client_Version0, v_R_System.Active0, v_R_System.Obsolete0, 
                         v_R_System.Unknown0, v_R_System.SMS_Unique_Identifier0, v_R_System.Object_GUID0, v_R_System.SMBIOS_GUID0, v_R_System.Previous_SMS_UUID0
FROM            v_R_System INNER JOIN
                         v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
                         v_GS_LastSoftwareScan ON v_R_System.ResourceID = v_GS_LastSoftwareScan.ResourceID
WHERE        (v_R_System.Name0 = @Computer)


Query 47 : User list for specific machine
SELECT        v_R_System.Name0, v_GS_COMPUTER_SYSTEM.Model0, v_GS_COMPUTER_SYSTEM.Manufacturer0, v_R_System.AD_Site_Name0, v_R_User.Full_User_Name0, 
                         v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0, v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0, v_GS_SYSTEM_CONSOLE_USER.TimeStamp, 
                         v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0
FROM            v_R_System INNER JOIN
                         v_GS_SYSTEM_CONSOLE_USER ON v_R_System.ResourceID = v_GS_SYSTEM_CONSOLE_USER.ResourceID INNER JOIN
                         v_R_User ON v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 = v_R_User.Unique_User_Name0 INNER JOIN
                         v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
WHERE        (v_R_System.Name0 = 'xxx')


Query 48 : Device drive states for a specific machine
SELECT        v_R_System.Name0, v_R_System.ResourceID, v_R_System.AD_Site_Name0, v_GS_PNP_DEVICE_DRIVER.Name0 AS 'Driver Name', 
                         v_GS_COMPUTER_SYSTEM.Model0, v_GS_PNP_DEVICE_DRIVER.TimeStamp, v_GS_PNP_DEVICE_DRIVER.ConfigManagerErrorCode0, 
                         v_GS_PNP_DEVICE_DRIVER.PNPDeviceID0, v_GS_PC_BIOS.Name0 AS BIOS_Name, v_GS_PC_BIOS.SMBIOSBIOSVersion0, v_GS_PC_BIOS.Description0, 
                         v_GS_PC_BIOS.Version0, v_GS_WORKSTATION_STATUS.LastHWScan
FROM            v_R_System INNER JOIN
                         v_GS_PNP_DEVICE_DRIVER ON v_R_System.ResourceID = v_GS_PNP_DEVICE_DRIVER.ResourceID INNER JOIN
                         v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID INNER JOIN
                         v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                         v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
WHERE        (v_R_System.Name0 = 'XX')


Query 49 : Summery of driver issues across environment
select distinct v_GS_PNP_DEVICE_DRIVER.Name0,
                v_GS_PNP_DEVICE_DRIVER.PNPDeviceID0,
                v_GS_PNP_DEVICE_DRIVER.ConfigManagerErrorCode0, 
                 COUNT(*) as Population 
from
                v_GS_PNP_DEVICE_DRIVER 
where 
      v_GS_PNP_DEVICE_DRIVER.ConfigManagerErrorCode0 <> 0 
group by 
       v_GS_PNP_DEVICE_DRIVER.Name0,v_GS_PNP_DEVICE_DRIVER.PNPDeviceID0,v_GS_PNP_DEVICE_DRIVER.ConfigManagerErrorCode0
order by
v_GS_PNP_DEVICE_DRIVER.Name0


Query 50 : Issues with a specific driver
SELECT        v_R_System.Name0, v_R_System.ResourceID, v_R_System.AD_Site_Name0, v_GS_PNP_DEVICE_DRIVER.Name0 AS 'Driver Name', 
                         v_GS_COMPUTER_SYSTEM.Model0, v_GS_PNP_DEVICE_DRIVER.TimeStamp, v_GS_PNP_DEVICE_DRIVER.ConfigManagerErrorCode0, 
                         v_GS_PNP_DEVICE_DRIVER.PNPDeviceID0, v_GS_PC_BIOS.Name0 AS BIOS_Name, v_GS_PC_BIOS.SMBIOSBIOSVersion0, v_GS_PC_BIOS.Description0, 
                         v_GS_PC_BIOS.Version0, v_GS_WORKSTATION_STATUS.LastHWScan
FROM            v_R_System INNER JOIN
                         v_GS_PNP_DEVICE_DRIVER ON v_R_System.ResourceID = v_GS_PNP_DEVICE_DRIVER.ResourceID INNER JOIN
                         v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID INNER JOIN
                         v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                         v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
WHERE        (v_GS_PNP_DEVICE_DRIVER.PNPDeviceID0 = 'PCI\VEN_8086&DEV_0C0C&SUBSYS_078D1025&REV_06\3&11583659&0&18') AND 
                         (v_GS_PNP_DEVICE_DRIVER.ConfigManagerErrorCode0 <> 0)


Query 51 : SCCM Applications and the content locations




SELECT    DISTINCT app.Manufacturer, app.DisplayName, app.SoftwareVersion, dt.DisplayName AS DeploymentTypeName, dt.PriorityInLatestApp, dt.Technology,
 v_ContentInfo.ContentSource, v_ContentInfo.SourceSize
 FROM         dbo.fn_ListDeploymentTypeCIs(1033) AS dt INNER JOIN
 dbo.fn_ListLatestApplicationCIs(1033) AS app ON dt.AppModelName = app.ModelName LEFT OUTER JOIN
 v_ContentInfo ON dt.ContentId = v_ContentInfo.Content_UniqueID
 WHERE     (dt.IsLatest = 1)

Credit to: http://anoopcnair.com/2012/05/07/configmgr-sccm-2012-custom-reports-for-list-of-applications-deployment-types-and-content-source/
Credit to: http://wiki.slothx.net/index.php?title=SCCM_2007_SQL_Queries#Query_10:_SCCM_Collections_with_Path_.28Working.29

Saturday, February 13, 2016

SCCM 2007 / 2012 SQL Queries

General

Notes

Forumns

SCCM Queries

Query 1: SCCM Advertisement Status (SCCM Report)

Query 2: SCCM Advertisement Status

Query 3: SCCM Advertisement, Package Type and Program Information with Target Count

Query 4: SCCM Adveritsment, Package Type and Program Information

Query 4a: SCCM Adveritsment, Package Type and Program Information - task sequences only

Query 5: SCCM Package and Package Type

Query 6: SCCM Advertisements showing Advertisement Path, Collection Path and Packge Path and Package Type

Query 7: SCCM Advertisements showing Advertisement Path, Collection Path and Packge Path

Query 8: SCCM Packages with Path

Query 9: SCCM Advertisements with Path

Query 10: SCCM Collections with Path (Working)

Query 11: SCCM Collections with Path

Query Group 11: Check Machines for Computer Association

Query 11a: Lookup Machine based on MAC Address

Query 11b: Lookup Machine and return MAC address details

Query 11c: Check MAC Address Table

Query 12: Lookup users Machine

Query 12: Lookup users Machine

Query 13: List computers marked as 'Unknown'

Query 14: Collection Analysis Group

Query 15: Collection Membership Analysis

Query 16: Security Patch Queries Group

Query 17: Misc Queries

Query 18: SCCM Driver Queries

Query 19: Add Remove Programs Search

Query 20: Basic Resource Data

Query 21 : Distribution Status - Percent complete (SCCM 2012)

Query 22 : Distribution Status - percent complete of in-progress packages (SCCM 2012)

Query 23 : Content View Queries (SCCM 2012)

Query 24 : Distribution Status (SCCM 2012)

Query 24a : Distribution Status - with Percentage Complete (SCCM 2012)

Query 24b : Distribution Status - Simple (SCCM 2012) - V3

Query 25 : Prestage settings (SCCM 2012)

Query 25 : Prestage settings (SCCM 2012)

Query 26 : Distribution Status - Multi-Purpose (SCCM 2012)

Query 27 : Prestage Settings (SCCM 2012)

Query 28 : Fix in SCCM DB when packages wont distribute

Query 29 : List all tables in SCCM

Query 30 : Collection Rules

Query 31 : List AD Sites from SCCM

Query 32 : Check SQL Fragmentation

Query 33 : Re-Index database

Query 34 : Return OS Info

Query 35 : Boundary Info

Query 36 : Distribution Points

Query 37a : Deployment Types

Query 37b : Deployment Types

Query 38 : Deployment Types - Task Sequences Only

Query 38a : All Package References for all task sequences

Query 38b : Task Sequences Deployments V2

Query 39a : Collection Rules

Query 39b : Collection Rules

Query 40 : Add/Remove Programs by Machine Name

Query 41 : Add/Remove Programs by Machine Name V2

Query 42 : Collection Type Analysis

Query 43 : Collection Type Count

Query 44 : AD Group Membership

Query 45 : Add/Remove Programs by Specific Software

Query 46 : Last Inventory Date

Query 47 : User list for specific machine

Query 48 : Device drive states for a specific machine

Query 49 : Summery of driver issues across environment

Query 50 : Issues with a specific driver

Query 51 : SCCM Applications and the content locations

No comments:

Post a Comment