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>
automatizeblog 