SCCM SQL Queries Mega-Pack

SOURCE : http://wiki.slothx.net/index.php?title=SCCM_2007_SQL_Queries

</pre>
<h3><span class="mw-headline">Query 1: SCCM Advertisement Status (SCCM Report)</span></h3>
<pre>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

</pre>
<pre><a name="Query_2:_SCCM_Advertisement_Status"></a></pre>
<h3><span class="mw-headline">Query 2: SCCM Advertisement Status</span></h3>
From <a class="external free" title="http://www.classictriple.com/sccm-the-best-advertisement-status-report/" href="http://www.classictriple.com/sccm-the-best-advertisement-status-report/" rel="nofollow">http://www.classictriple.com/sccm-the-best-advertisement-status-report/</a>
<pre>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

</pre>
<pre><a name="Query_3:_SCCM_Advertisement.2C_Package_Type_and_Program_Information_with_Target_Count"></a></pre>
<h3><span class="mw-headline">Query 3: SCCM Advertisement, Package Type and Program Information with Target Count</span></h3>
<pre>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
</pre>
<pre><a name="Query_4:_SCCM_Adveritsment.2C_Package_Type_and_Program_Information"></a></pre>
<h3><span class="mw-headline">Query 4: SCCM Adveritsment, Package Type and Program Information</span></h3>
<pre>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
                      
</pre>
<pre><a name="Query_4a:_SCCM_Adveritsment.2C_Package_Type_and_Program_Information_-_task_sequences_only"></a></pre>
<h3><span class="mw-headline">Query 4a: SCCM Adveritsment, Package Type and Program Information - task sequences only</span></h3>
<pre>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                      
</pre>
<pre><a name="Query_5:_SCCM_Package_and_Package_Type"></a></pre>
<h3><span class="mw-headline">Query 5: SCCM Package and Package Type</span></h3>
<pre>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
</pre>
<pre><a name="Query_6:_SCCM_Advertisements_showing_Advertisement_Path.2C_Collection_Path_and_Packge_Path_and_Package_Type"></a></pre>
<h3><span class="mw-headline">Query 6: SCCM Advertisements showing Advertisement Path, Collection Path and Packge Path and Package Type</span></h3>
<pre>-- 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 * --<<-- need to select the specific columns you need
FROM (
    SELECT        v_Advertisement.AdvertisementID, v_Advertisement.AdvertisementName, v_Package.PackageID, v_Package.Name as Package_Name,
    v_Package.PackageType,
    '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 = 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
    FROM            v_Advertisement INNER JOIN
                             v_Package ON v_Advertisement.PackageID = v_Package.PackageID INNER JOIN
                             v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
) AS combined1
INNER JOIN (
    SELECT     fldr.CollectionID, fldr.Name as Collection_Name, fldr.Path as Collection_Path
    FROM         folderHierarchy_Collections AS fldr
) AS [collection] ON
    combined1.CollectionID = [collection].CollectionID
INNER JOIN (
    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
) AS packages ON
    combined1.PackageID = packages.PackageID 
INNER JOIN (
    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
) AS advertisements ON
combined1.AdvertisementID = advertisements.AdvertisementID

</pre>
<pre><a name="Query_7:_SCCM_Advertisements_showing_Advertisement_Path.2C_Collection_Path_and_Packge_Path"></a></pre>
<h3><span class="mw-headline">Query 7: SCCM Advertisements showing Advertisement Path, Collection Path and Packge Path</span></h3>
<pre>-- 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 * --<<-- need to select the specific columns you need
FROM (
    SELECT        v_Advertisement.AdvertisementID, v_Advertisement.AdvertisementName, v_Package.PackageID, v_Package.Name as Package_Name, v_Collection.CollectionID, 
                         v_Collection.Name AS CollectionName
    FROM            v_Advertisement INNER JOIN
                             v_Package ON v_Advertisement.PackageID = v_Package.PackageID INNER JOIN
                             v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
) AS combined1
INNER JOIN (
    SELECT     fldr.CollectionID, fldr.Name as Collection_Name, fldr.Path as Collection_Path
    FROM         folderHierarchy_Collections AS fldr
) AS [collection] ON
    combined1.CollectionID = [collection].CollectionID
INNER JOIN (
    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
) AS packages ON
    combined1.PackageID = packages.PackageID 
INNER JOIN (
    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
) AS advertisements ON
combined1.AdvertisementID = advertisements.AdvertisementID
</pre>
<pre><a name="Query_8:_SCCM_Packages_with_Path"></a></pre>
<h3><span class="mw-headline">Query 8: SCCM Packages with Path</span></h3>
<pre>--- 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

</pre>
<pre><a name="Query_9:_SCCM_Advertisements_with_Path"></a></pre>
<h3><span class="mw-headline">Query 9: SCCM Advertisements with Path</span></h3>
<pre>--- 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

</pre>
<pre><a name="Query_10:_SCCM_Collections_with_Path_.28Working.29"></a></pre>
<h3><span class="mw-headline">Query 10: SCCM Collections with Path (Working)</span></h3>
<pre>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
</pre>
<pre><a name="Query_11:_SCCM_Collections_with_Path"></a></pre>
<h3><span class="mw-headline">Query 11: SCCM Collections with Path</span></h3>
<pre>-- 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

</pre>
<pre><a name="Query_Group_11:_Check_Machines_for_Computer_Association"></a></pre>
<h3><span class="mw-headline">Query Group 11: Check Machines for Computer Association</span></h3>
<pre>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'
</pre>
<pre><a name="Query_11a:_Lookup_Machine_based_on_MAC_Address"></a></pre>
<h3><span class="mw-headline">Query 11a: Lookup Machine based on MAC Address</span></h3>
<pre>-- 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'
</pre>
<pre><a name="Query_11b:_Lookup_Machine_and_return_MAC_address_details"></a></pre>
<h3><span class="mw-headline">Query 11b: Lookup Machine and return MAC address details</span></h3>
<pre>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%'
</pre>
<pre><a name="Query_11c:_Check_MAC_Address_Table"></a></pre>
<h3><span class="mw-headline">Query 11c: Check MAC Address Table</span></h3>
<pre>select * from v_RA_System_MACAddresses where v_RA_System_MACAddresses.MAC_Addresses0 = '00:00:00:00:00:00'
</pre>
<pre><a name="Query_12:_Lookup_users_Machine"></a></pre>
<h3><span class="mw-headline">Query 12: Lookup users Machine</span></h3>
<pre>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')
</pre>

<hr />

<pre><a name="Query_12:_Lookup_users_Machine_2"></a></pre>
<h3><span class="mw-headline">Query 12: Lookup users Machine</span></h3>
<pre>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')
</pre>

<hr />

<pre><a name="Query_13:_List_computers_marked_as_.27Unknown.27"></a></pre>
<h3><span class="mw-headline">Query 13: List computers marked as 'Unknown'</span></h3>
<pre>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')
</pre>
<pre><a name="Query_14:_Collection_Analysis_Group"></a></pre>
<h3><span class="mw-headline">Query 14: Collection Analysis Group</span></h3>
<pre>select * from v_CollectionRuleDirect
select * from v_CollectionRuleQuery
select * from Collection_Rules_SQL
select * from Collection_Rules
</pre>
<pre><a name="Query_15:_Collection_Membership_Analysis"></a></pre>
<h3><span class="mw-headline">Query 15: Collection Membership Analysis</span></h3>
<b>SCCM 2012</b>
<pre>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')
</pre>
<b>SCCM 2007</b>
<pre>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')
</pre>
<pre><a name="Query_16:_Security_Patch_Queries_Group"></a></pre>
<h3><span class="mw-headline">Query 16: Security Patch Queries Group</span></h3>
<pre>-- 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
</pre>
<pre>-- 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
</pre>
<pre><a name="Query_17:_Misc_Queries"></a></pre>
<h3><span class="mw-headline">Query 17: Misc Queries</span></h3>
<pre>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
</pre>
<pre><a name="Query_18:_SCCM_Driver_Queries"></a></pre>
<h3><span class="mw-headline">Query 18: SCCM Driver Queries</span></h3>
<pre>select * from v_CategoryInfo

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

SELECT DISTINCT CategoryInstanceName FROM v_CategoryInfo WHERE (CategoryTypeName = 'DriverCategories')
</pre>
<pre><a name="Query_19:_Add_Remove_Programs_Search"></a></pre>
<h3><span class="mw-headline">Query 19: Add Remove Programs Search</span></h3>
<pre>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')
</pre>
<pre><a name="Query_20:_Basic_Resource_Data"></a></pre>
<h3><span class="mw-headline">Query 20: Basic Resource Data</span></h3>
<pre>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
</pre>
<pre><a name="Query_21_:_Distribution_Status_-_Percent_complete_.28SCCM_2012.29"></a></pre>
<h3><span class="mw-headline">Query 21 : Distribution Status - Percent complete (SCCM 2012)</span></h3>
<pre>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
</pre>
<pre><a name="Query_22_:_Distribution_Status_-_percent_complete_of_in-progress_packages_.28SCCM_2012.29"></a></pre>
<h3><span class="mw-headline">Query 22 : Distribution Status - percent complete of in-progress packages (SCCM 2012)</span></h3>
<pre>-- 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)

</pre>
<pre><a name="Query_23_:_Content_View_Queries_.28SCCM_2012.29"></a></pre>
<h3><span class="mw-headline">Query 23 : Content View Queries (SCCM 2012)</span></h3>
<pre>-- 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
</pre>
<pre><a name="Query_24_:_Distribution_Status_.28SCCM_2012.29"></a></pre>
<h3><span class="mw-headline">Query 24 : Distribution Status (SCCM 2012)</span></h3>
<pre>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

</pre>
<pre><a name="Query_24a_:_Distribution_Status_-_with_Percentage_Complete_.28SCCM_2012.29"></a></pre>
<h3><span class="mw-headline">Query 24a : Distribution Status - with Percentage Complete (SCCM 2012)</span></h3>
<pre>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
</pre>
<pre><a name="Query_24b_:_Distribution_Status_-_Simple_.28SCCM_2012.29_-_V3"></a></pre>
<h3><span class="mw-headline">Query 24b : Distribution Status - Simple (SCCM 2012) - V3</span></h3>
<pre>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
</pre>
<pre><a name="Query_25_:_Prestage_settings_.28SCCM_2012.29"></a></pre>
<h3><span class="mw-headline">Query 25 : Prestage settings (SCCM 2012)</span></h3>
<pre> -- 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
</pre>
<pre><a name="Query_25_:_Prestage_settings_.28SCCM_2012.29_2"></a></pre>
<h3><span class="mw-headline">Query 25 : Prestage settings (SCCM 2012)</span></h3>
<pre> -- 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
</pre>
<pre><a name="Query_26_:_Distribution_Status_-_Multi-Purpose_.28SCCM_2012.29"></a></pre>
<h3><span class="mw-headline">Query 26 : Distribution Status - Multi-Purpose (SCCM 2012)</span></h3>
<pre>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	
</pre>
<pre><a name="Query_27_:_Prestage_Settings_.28SCCM_2012.29"></a></pre>
<h3><span class="mw-headline">Query 27 : Prestage Settings (SCCM 2012)</span></h3>
<pre> -- 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
</pre>
<pre><a name="Query_28_:_Fix_in_SCCM_DB_when_packages_wont_distribute"></a></pre>
<h3><span class="mw-headline">Query 28 : Fix in SCCM DB when packages wont distribute</span></h3>
<pre># Use the following carefully!

delete from PkgServers where PkgID = 'XXX' and NALPath like '%XXX%'
</pre>
<pre><a name="Query_29_:_List_all_tables_in_SCCM"></a></pre>
<h3><span class="mw-headline">Query 29 : List all tables in SCCM</span></h3>
<pre>sp_MSforeachtable @command1="print '?'"
</pre>
<pre><a name="Query_30_:_Collection_Rules"></a></pre>
<h3><span class="mw-headline">Query 30 : Collection Rules</span></h3>
<pre>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
</pre>
<pre><a name="Query_31_:_List_AD_Sites_from_SCCM"></a></pre>
<h3><span class="mw-headline">Query 31 : List AD Sites from SCCM</span></h3>
<pre>select distinct AD_Site_Name0 from vSMS_R_System order by AD_Site_Name0
</pre>
<pre><a name="Query_32_:_Check_SQL_Fragmentation"></a></pre>
<h3><span class="mw-headline">Query 32 : Check SQL Fragmentation</span></h3>
<pre>-- 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);

</pre>
<pre><a name="Query_33_:_Re-Index_database"></a></pre>
<h3><span class="mw-headline">Query 33 : Re-Index database</span></h3>
<pre>-- 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 ('?')"
</pre>
<pre><a name="Query_34_:_Return_OS_Info"></a></pre>
<h3><span class="mw-headline">Query 34 : Return OS Info</span></h3>
<pre>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%')
</pre>
<pre><a name="Query_35_:_Boundary_Info"></a></pre>
<h3><span class="mw-headline">Query 35 : Boundary Info</span></h3>
<pre>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

</pre>
<pre><a name="Query_36_:_Distribution_Points"></a></pre>
<h3><span class="mw-headline">Query 36 : Distribution Points</span></h3>
<pre>SELECT DISTINCT ServerName FROM fn_rbac_DistributionPointInfo('Disabled') order by ServerName
</pre>
<pre><a name="Query_37a_:_Deployment_Types"></a></pre>
<h3><span class="mw-headline">Query 37a : Deployment Types</span></h3>
<pre>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
</pre>
<pre><a name="Query_37b_:_Deployment_Types"></a></pre>
<h3><span class="mw-headline">Query 37b : Deployment Types</span></h3>
<pre>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
</pre>
<pre><a name="Query_38_:_Deployment_Types_-_Task_Sequences_Only"></a></pre>
<h3><span class="mw-headline">Query 38 : Deployment Types - Task Sequences Only</span></h3>
<pre>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
</pre>
<pre><a name="Query_38a_:_All_Package_References_for_all_task_sequences"></a></pre>
<h3><span class="mw-headline">Query 38a : All Package References for all task sequences</span></h3>
<pre>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)
</pre>
<pre><a name="Query_38b_:_Task_Sequences_Deployments_V2"></a></pre>
<h3><span class="mw-headline">Query 38b : Task Sequences Deployments V2</span></h3>
<pre>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)
</pre>
<pre><a name="Query_39a_:_Collection_Rules"></a></pre>
<h3><span class="mw-headline">Query 39a : Collection Rules</span></h3>
<pre>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')
</pre>
<pre><a name="Query_39b_:_Collection_Rules"></a></pre>
<h3><span class="mw-headline">Query 39b : Collection Rules</span></h3>
<pre>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')
</pre>
<pre><a name="Query_40_:_Add.2FRemove_Programs_by_Machine_Name"></a></pre>
<h3><span class="mw-headline">Query 40 : Add/Remove Programs by Machine Name</span></h3>
<pre>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
</pre>
<pre><a name="Query_41_:_Add.2FRemove_Programs_by_Machine_Name_V2"></a></pre>
<h3><span class="mw-headline">Query 41 : Add/Remove Programs by Machine Name V2</span></h3>
<pre>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'

</pre>
<pre><a name="Query_42_:_Collection_Type_Analysis"></a></pre>
<h3><span class="mw-headline">Query 42 : Collection Type Analysis</span></h3>
<pre>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
</pre>
<pre><a name="Query_43_:_Collection_Type_Count"></a></pre>
<h3><span class="mw-headline">Query 43 : Collection Type Count</span></h3>
<pre>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
</pre>
<pre><a name="Query_44_:_AD_Group_Membership"></a></pre>
<h3><span class="mw-headline">Query 44 : AD Group Membership</span></h3>
<pre>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
</pre>
<pre><a name="Query_45_:_Add.2FRemove_Programs_by_Specific_Software"></a></pre>
<h3><span class="mw-headline">Query 45 : Add/Remove Programs by Specific Software</span></h3>
<pre>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

</pre>
<pre><a name="Query_46_:_Last_Inventory_Date"></a></pre>
<h3><span class="mw-headline">Query 46 : Last Inventory Date</span></h3>
<pre>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)
</pre>
<pre><a name="Query_47_:_User_list_for_specific_machine"></a></pre>
<h3><span class="mw-headline">Query 47 : User list for specific machine</span></h3>
<pre>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')
</pre>
<pre><a name="Query_48_:_Device_drive_states_for_a_specific_machine"></a></pre>
<h3><span class="mw-headline">Query 48 : Device drive states for a specific machine</span></h3>
<pre>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')
</pre>
<pre><a name="Query_49_:_Summery_of_driver_issues_across_environment"></a></pre>
<h3><span class="mw-headline">Query 49 : Summery of driver issues across environment</span></h3>
<pre>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
</pre>
<pre><a name="Query_50_:_Issues_with_a_specific_driver"></a></pre>
<h3><span class="mw-headline">Query 50 : Issues with a specific driver</span></h3>
<pre>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)
</pre>
<pre><a name="Query_51_:_SCCM_Applications_and_the_content_locations"></a></pre>
<h3><span class="mw-headline">Query 51 : SCCM Applications and the content locations</span></h3>
(Credit to: <a class="external free" title="http://anoopcnair.com/2012/05/07/configmgr-sccm-2012-custom-reports-for-list-of-applications-deployment-types-and-content-source/" href="http://anoopcnair.com/2012/05/07/configmgr-sccm-2012-custom-reports-for-list-of-applications-deployment-types-and-content-source/" rel="nofollow">http://anoopcnair.com/2012/05/07/configmgr-sccm-2012-custom-reports-for-list-of-applications-deployment-types-and-content-source/</a>)
<pre>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)</pre>
<pre>





Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s