SQL Query – endusersupports.com

Here is a list of SQL queries to assist with common reporting tasks

SQL Query About Clients.

select sys.Name0 as ‘Host Name’,
fcm.CollectionID as ‘Collection ID’,
vc.Name as ‘Collection Name’,
sys.Operating_System_Name_and0 as ‘O.S. Name’,
sys.Build01 as ‘Build Version’
from v_R_System sys
inner join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
inner join v_Collection vc on vc.CollectionID = fcm.CollectionID
where fcm.CollectionID = ‘SMS00001’

Below is the output.

To Check machine part of which collection

Below is the output.

List of Client Version Count and list

To get the client counts

select sys.Client_Version0 as ‘Client Version’,
count(sys.Client_Version0) as ‘Client Count’
from v_R_System sys
group by sys.Client_Version0
order by sys.Client_Version0 desc

Below is the output.

Get the Machine List.

select sys.Name0,
sys.Client_Version0 as ‘Client Version’
from v_R_System sys

Below is the output.

List of Operating System Count and list

To Get the Operating System Count

select sys.Operating_System_Name_and0 as ‘O.S. Name’,
Count(sys.Operating_System_Name_and0) as ‘Count’
from v_R_System sys
group by sys.Operating_System_Name_and0

Below is the output

To get machine list.

select sys.Name0,
sys.Operating_System_Name_and0 as ‘O.S. Name’
from v_R_System sys

Below is the output

Client Health Status

select
sys.Name0 as ‘Computer Name’,
sys.User_Name0 as ‘User Name’,
VCS.ClientStateDescription,
case when VCS.ClientActiveStatus = 0 then ‘Inactive’
when VCS.ClientActiveStatus = 1 then ‘Active’
end as ‘ClientActiveStatus’,
vcs.LastActiveTime,
case when VCS.IsActiveDDR = 0 then ‘Inactive’
when VCS.IsActiveDDR = 1 then ‘Active’
end as ‘IsActiveDDR’,
case when VCS.IsActiveHW = 0 then ‘Inactive’
when VCS.IsActiveHW = 1 then ‘Active’
end as ‘IsActiveHW’,
case when VCS.IsActiveSW = 0 then ‘Inactive’
when VCS.IsActiveSW = 1 then ‘Active’
end as ‘IsActiveSW’,
case when VCS.ISActivePolicyRequest = 0 then ‘Inactive’
when VCS.ISActivePolicyRequest = 1 then ‘Active’
end as ‘ISActivePolicyRequest’,
case when VCS.IsActiveStatusMessages = 0 then ‘Inactive’
when VCS.IsActiveStatusMessages = 1 then ‘Active’
end as ‘IsActiveStatusMessages’,
VCS.LastOnline,
VCS.LastDDR,
VCS.LastHW,
VCS.LastSW,
VCS.LastPolicyRequest,
VCS.LastStatusMessage,
VCS.LastHealthEvaluation,
case when LastHealthEvaluationResult = 1 then ‘Not Yet Evaluated’
when LastHealthEvaluationResult = 2 then ‘Not Applicable’
when LastHealthEvaluationResult = 3 then ‘Evaluation Failed’
when LastHealthEvaluationResult = 4 then ‘Evaluated Remediated Failed’
when LastHealthEvaluationResult = 5 then ‘Not Evaluated Dependency Failed’
when LastHealthEvaluationResult = 6 then ‘Evaluated Remediated Succeeded’
when LastHealthEvaluationResult = 7 then ‘Evaluation Succeeded’
end as ‘Last Health Evaluation Result’,
case when LastEvaluationHealthy = 1 then ‘Pass’
when LastEvaluationHealthy = 2 then ‘Fail’
when LastEvaluationHealthy = 3 then ‘Unknown’
end as ‘Last Evaluation Healthy’,
case when VCS.ClientRemediationSuccess = 1 then ‘Pass’
when VCS.ClientRemediationSuccess = 2 then ‘Fail’
else ”
end as ‘ClientRemediationSuccess’,
VCS.ExpectedNextPolicyRequest
from v_R_System sys
full join v_CH_ClientSummary VCS on VCS.ResourceID = sys.ResourceID

Below is the output.

Client Deployed Status

declare @Total int
declare @Succeeded int
declare @Failed int
declare @InProgress int

select @Succeeded = count (*) from ClientDeploymentState where DeploymentEndTime is not NULL

select @Failed = count(*) from ClientDeploymentState where LastMessageStateID>100 AND LastMessageStateID<400

select @InProgress= count(*) from ClientDeploymentState where DeploymentEndTime is NULL and LastMessageStateID = 100

select @Total = @Failed + @InProgress + @Succeeded

if @Total > 0
begin
select @Total as ComputersWithStatus, @Succeeded as DeploySucceeded, @Failed as DeployFailed, @InProgress as DeployInProgress, 1.00*@Succeeded/@Total as SuccessRate, 1.00*@Failed/@Total as FailureRate, 1.00*@InProgress/@Total as InProgressRate
end

Client Assignment Failure Status

select
NetBiosName as ‘Machine Name’,
ClientVersion as ‘Client Version’,
AssignedSiteCode as SiteCode,
AssignmentBeginTime as AssignmentStartTime,
LastMessageParam as Description
from ClientDeploymentState where LastMessageStateID > 500 and LastMessageStateID < 700

Duplicate MAC Address

select distinct fcm.Name,
fcm.SiteCode,
mac1.MAC_Addresses0,
MAX(ad.AgentTime) as AgentTime
from v_FullCollectionMembership fcm
join v_AgentDiscoveries ad on fcm.ResourceID=ad.ResourceId and fcm.CollectionID=’SMS00001′
join (select mac2.* from v_RA_System_MACAddresses mac2
join (select MAC_Addresses0 from v_RA_System_MACAddresses group by MAC_Addresses0
having count(MAC_Addresses0) > 1) m3 on mac2.MAC_Addresses0 = m3.MAC_Addresses0) mac1 on fcm.ResourceID=mac1.ResourceID
group by mac1.MAC_Addresses0, fcm.Name, fcm.SiteCode
order by mac1.MAC_Addresses0, MAX(ad.AgentTime)

Software Update(Patches) Compliance Required, Missing and Deployment Status Query

To Check Patch Status (Installed, Required, Unknown) count per Machine

Bellow is the query to get the Patch details like Installed, Missing, Not Required and Unknows count per machine.

SELECT
vr.Netbios_Name0 as ‘Host Name’,
count(ucs.status) as ‘Total Patches’,
SUM (CASE WHEN ucs.status=3 then 1 ELSE 0 END ) as ‘Installed’,
sum( case When ucs.status=2 Then 1 ELSE 0 END ) as ‘Required’,
sum( case When ucs.status=0 Then 1 ELSE 0 END ) as ‘Unknown’,
sum( case When ucs.status=1 Then 1 ELSE 0 END ) as ‘Not Required’
FROM v_Update_ComplianceStatusAll AS UCS
INNER JOIN fn_ListUpdateCIs(1033) AS UPD ON UCS.CI_ID=UPD.CI_ID
INNER JOIN v_R_System AS vr ON VR.ResourceID = UCS.ResourceID
INNER JOIN vSMS_CIRelation as cr on upd.CI_ID = cr.ToCIID AND cr.RelationType = 1
INNER JOIN v_AuthListInfo AL ON al.CI_ID =cr.FromCIID
inner join v_FullCollectionMembership fcm on fcm.ResourceID=vr.ResourceID
WHERE
fcm.CollectionID = ‘DEL0002E’
and AL.Title = ‘June_Month_Patch_Ddeployment_Servers’
group by vr.Netbios_Name0

Below is the output for the query

Patches status per Machine (How many patches Installed or Missing))

SELECT
vr.Netbios_Name0 as ‘Host Name’,
substring(upd.DisplayName,(CHARINDEX(‘KB’,upd.DisplayName)),9) as ‘Patch KB’,
upd.DisplayName,
CASE WHEN ucs.status=3 then ‘Installed’
When ucs.status=2 Then ‘Required’
When ucs.status=0 Then ‘Unknown’
When ucs.status=1 Then ‘Not Required’
else ‘Unknown’
end as ‘Patch Status’,
Case when ccs.ClientActiveStatus = ‘1’ then ‘Active’
when ccs.ClientActiveStatus = ‘0’ then ‘InActive’
else ‘NA’ end as ‘Client Satus’,
upd.SeverityName,
ccs.LastHW,
DATEDIFF(d,ccs.LastHW,GETDATE()) as ‘HW Scan in Days’

FROM v_Update_ComplianceStatusAll AS UCS
INNER JOIN fn_ListUpdateCIs(1033) AS UPD ON UCS.CI_ID=UPD.CI_ID
INNER JOIN v_R_System AS vr ON VR.ResourceID = UCS.ResourceID
INNER JOIN vSMS_CIRelation as cr on upd.CI_ID = cr.ToCIID AND cr.RelationType = 1
INNER JOIN v_AuthListInfo AL ON al.CI_ID =cr.FromCIID
inner join v_FullCollectionMembership fcm on fcm.ResourceID=vr.ResourceID
inner join v_CH_ClientSummary ccs on ccs.ResourceID = ucs.ResourceID
WHERE
fcm.CollectionID = ‘DEL0002E’
and AL.Title = ‘June_Month_Patch_Ddeployment_Servers’
order by vr.Name0

Below is the query output.

One Patches Status on Machines

Below is output for Query

On one Machine Patches Status

Below Is the Output for Query

Over All Patches Compliance Count and Percentage

select
count(*) as ‘Total Client’,
Sum(Case when UCS.Status = 3 or UCS.Status = 1 then 1 else 0 end ) as ‘Compliance’,
Sum(Case when UCS.Status = 2 then 1 else 0 end ) as ‘Required’,
Sum(Case when UCS.Status = 0 then 1 else 0 end ) as ‘Unknown’,
round((Cast(Sum(Case when UCS.Status = 3 or UCS.Status = 1 then 1 else 0 end ) as float)/count(*))*100,2) as ‘Compliance %’,
round((Cast(Sum(Case when UCS.Status = 2 then 1 else 0 end ) as float)/count(*))*100,2) as ‘Required %’,
round((Cast(Sum(Case when UCS.Status = 0 then 1 else 0 end) as float)/count(*))*100,2) as ‘Unknown %’

FROM v_Update_ComplianceStatusAll AS UCS
INNER JOIN v_R_System AS sys ON UCS.ResourceID = sys.ResourceID
inner join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
inner join v_Collection coll on coll.CollectionID = fcm.CollectionID
INNER JOIN v_AuthListInfo LI ON UCS.CI_ID = LI.CI_ID
inner join v_CH_ClientSummary ccs on ccs.ResourceID = ucs.ResourceID

where LI.Title = ‘June_Month_Patch_Ddeployment_Servers’
and coll.CollectionID = ‘DEL0002E’

Below is the Output for Query

Patches Deployment Compliance (Active Deployment)

declare @DeploymentLocalID as int = (select AssignmentID from v_CIAssignment where Assignment_UniqueID= ‘{C5B8B6B9-E540-42C1-9231-849818693DFC}’)
declare @COLLCOUNT int = (select count(*) from v_CIAssignmentTargetedMachines where AssignmentID=@DeploymentLocalID)
select
sn.StateName as ‘Status’,
sc.StateCount as ‘Machine Count’,
convert(float, sc.StateCount*100.0) / isnull(nullif(@COLLCOUNT, 0), 1) as ‘Status %’
from v_CIAssignment a
cross apply(select StateType, StateID, StateCount=count(*) from v_AssignmentState_Combined
where AssignmentID=a.AssignmentID and StateType in (300,301) group by StateType, StateID) sc
left join v_StateNames sn on sn.TopicType=sc.StateType and sn.StateID=sc.StateID
where a.AssignmentID= @DeploymentLocalID
order by sc.StateCount desc, sn.StateName

Here is output for the query

Package Details, Status and Deployment Query

Package Deployment Status with Deployment ID

select
vc.Name as ‘CollectionName’,
pkg.Name as ‘Pacakge Name’,
sys.Name0 as ‘Host Name’,
sys.User_Name0 as ‘User Name’,
ads.LastAcceptanceStateName as ‘Package Acceptance Status’,
ads.LastAcceptanceStatusTime as ‘Last Stuats’,
ads.LastStateName as ‘Package Status’,
ads.LastStatusMessageIDName as ‘Last Message ID’,
ads.LastExecutionResult as ‘Execution Result’
from v_Advertisement as adv
inner join v_Package pkg on pkg.PackageID = adv.PackageID
inner join v_ClientAdvertisementStatus as ads on adv.AdvertisementID=ads.AdvertisementID
inner join v_R_System as sys on ads.ResourceID=sys.ResourceID
inner join v_Collection as VC on adv.CollectionID=VC.CollectionID
Where adv.AdvertisementID = ‘DEL20003’
order by ads.LastStateName

Below is the output

Package Deployment State

select stat.LastStateName as ‘Status’,
count(*) as ‘Counts’,
ROUND(100.0*count(*)/sum(case LastState when 0 then 0 else 1 end),1) as ‘Compliance %’
from v_ClientAdvertisementStatus stat
join v_Advertisement advert on stat.AdvertisementID = advert.AdvertisementID
where stat.AdvertisementID= ‘XXXXXXXXX’ and LastState!=0
group by LastStateName, stat.AdvertisementID, advert.PackageID, advert.CollectionID

Below is the output

Package Deployment Status

declare @Total int
declare @Accepted int
select @Total=count(*),
@Accepted=sum(case LastState when 0 then 0 else 1 end)
from v_ClientAdvertisementStatus
where AdvertisementID=’XXXXXXXXX’
select LastAcceptanceStateName as Status,
count(*) as Counts,
ROUND(100.0*count(*)/@Total,1) as ‘Compliance %’
from v_ClientAdvertisementStatus stat
join v_Advertisement advert on stat.AdvertisementID = advert.AdvertisementID
where stat.AdvertisementID= ‘XXXXXXX’
group by LastAcceptanceStateName, stat.AdvertisementID, advert.PackageID, advert.CollectionID

Below is the output

All Packages available In SCCM Console

select pkg.PackageID as ‘Pacakge ID’,
pkg.Name as ‘Package Name’,
Pkg.Version as ‘Package Version’,
pkg.PkgSourcePath as ‘Package Path’,
pkg.SourceDate as ‘Pacakge Created Date’,
pkg.SourceSite as ‘Last Update Pacakge’,
pkg.LastRefreshTime
from v_Package pkg
where pkg.PackageType =’0′
and pkg.Name != ‘User State Migration Tool for Windows’
and pkg.Name != ‘Configuration Manager Client Package’
and pkg.Name != ‘Configuration Manager Client Upgrade Package’

Below is the Query Output

Package Distributed Summary to D.P.'s

select pkg.PackageID,
pkg.Name,
vpkg.Targeted,
vpkg.Installed,
vpkg.Pending,
vpkg.Retrying,
vpkg.Failed,
vpkg.Unknown,
(vpkg.SourceSize /1024) as ‘Package Size(MB)’,
pkg.PkgSourcePath

from vPkgStatusSummary vpkg
inner join v_Package pkg on pkg.PackageID = vpkg.PkgID
where pkg.PackageType =’0′
and pkg.Name != ‘User State Migration Tool for Windows’
and pkg.Name != ‘Configuration Manager Client Package’
and pkg.Name != ‘Configuration Manager Client Upgrade Package’

Below is the output for the query

Packages Distribution Status on D.P.

select pkg.PackageID as ‘Pacakge ID’,
pkg.Name as ‘Package Name’,
Pkg.Version as ‘Package Version’,
pkg.PkgSourcePath as ‘Package Path’,
pkg.SourceDate as ‘Pacakge Created Date’,
pkg.SourceSite as ‘Last Update Pacakge’,
pkg.LastRefreshTime,
pkgs.ServerNALPath as ‘Pacakge DP source’,
pkgs.SummaryDate ‘Pacakge Distributed Date’,
pkgs.InstallStatus as ‘Pacakge Distributed status’
from v_Package pkg
inner join v_PackageStatusDistPointsSumm pkgs on pkgs.PackageID= pkg.PackageID
where pkg.PackageType =’0′
and pkg.Name != ‘User State Migration Tool for Windows’
and pkg.Name != ‘Configuration Manager Client Package’
and pkg.Name != ‘Configuration Manager Client Upgrade Package’
and Pkgs.ServerNALPath like ‘%SCCM.vansh.com%’

Below is the output

Package not Distributed to any D.P.

select pkg.PackageID as ‘Pacakge ID’,
pkg.Name as ‘Package Name’,
Pkg.Version as ‘Package Version’,
pkg.PkgSourcePath as ‘Package Path’,
pkg.SourceDate as ‘Pacakge Created Date’,
pkg.SourceSite as ‘Last Update Pacakge’,
pkg.LastRefreshTime,
pkgs.ServerNALPath as ‘Pacakge DP source’,
pkgs.SummaryDate ‘Pacakge Distributed Date’,
pkgs.InstallStatus as ‘Pacakge Distributed status’
from v_Package pkg
full join v_PackageStatusDistPointsSumm pkgs on pkgs.PackageID= pkg.PackageID
where pkg.PackageType =’0′
and pkg.Name != ‘User State Migration Tool for Windows’
and pkg.Name != ‘Configuration Manager Client Package’
and pkg.Name != ‘Configuration Manager Client Upgrade Package’
and Pkgs.ServerNALPath is null

Below is the output

Application Details, Status and Deployment Status Query

Application Deployment Status (Specific Application)

SELECT ApplicationName, CollectionID, AssignmentID, ParentID, name as ‘Application Status’, count as ‘Status Count’

FROM
(
SELECT
DS.Success Installed,
DS.InProgress InProgress,
DS.AlreadyPresent AlreadyPresent,
DS.Error Errors,
DS.RequirementsNotMet ReqsNotMet,
DS.CI_ID AS DTCI,
DS.Descript AS SoftwareName,
DS.TargetCollectionID AS CollectionID,
DS.CI_ID AS AppCI,
DS.AssignmentID,
DS.ParentID,
ast.ApplicationName
FROM v_AppDTDeploymentSummary AS DS
inner join v_ApplicationAssignment AST on AST.AssignmentID= ds.AssignmentID
WHERE
DS.TargetCollectionID = ‘DEL00029’
AND DS.AssignmentID = ‘16777222’
) a
UNPIVOT
(
count FOR name IN (Installed, InProgress, AlreadyPresent, Errors, ReqsNotMet)
) AS b

All Application Deployment Status

SELECT * FROM
(
SELECT DISTINCT

DSC.CollectionID as CollectionID,
DSC.AssignmentID,
DSC.CollectionName,
DSC.SoftwareName as SoftwareName,
DSC.DeploymentTime as DeploymentTime,
DSC.ModificationTime,
AA.LastModifiedBy as LastModifiedBy,
DSC.NumberSuccess NumberSuccess,
DSC.NumberInProgress,
DSC.NumberUnknown,
DSC.NumberOther NumberOther,
DSC.NumberErrors,
(DSC.NumberSuccess + DSC.NumberInProgress + DSC.NumberErrors + DSC.NumberOther + DSC.NumberUnknown) TotalDeployments
FROM v_DeploymentSummary DSC
JOIN v_ApplicationAssignment AA on AA.AssignmentID = DSC.AssignmentID
JOIN
( SELECT 1 as Id, ‘ApplicationType1’ as typeId union all
SELECT 2 as Id, ‘ApplicationType2’ as typeId union all
SELECT 3 as Id, ‘ApplicationType3’ as typeId union all
SELECT 4 as Id, ‘ApplicationType4’ as typeId union all
SELECT 5 as Id, ‘ApplicationType5’ as typeId union all
SELECT 6 as Id, ‘ApplicationType6’ as typeId union all
SELECT 7 as Id, ‘ApplicationType7’ as typeId ) apptypes on apptypes.Id = DSC.FeatureType
) temp

All Applications Available in SCCM Console

Below is the Output

All Application Status Distributed on DP,s

select pkg.PackageID as ‘Package ID’,
pkg.Name as ‘Package Name’,
Pkg.Version as ‘Package Version’,
pkg.PkgSourcePath as ‘Package Path’,
pkg.SourceDate as ‘Pacakge Created Date’,
pkg.SourceSite as ‘Last Update Pacakge’,
pkg.LastRefreshTime,
pkgs.ServerNALPath as ‘Pacakge DP source’,
pkgs.SummaryDate ‘Pacakge Distributed Date’,
pkgs.InstallStatus as ‘Pacakge Distributed status’
from v_Package pkg
full join v_PackageStatusDistPointsSumm pkgs on pkgs.PackageID= pkg.PackageID
where pkg.PackageType =’8′

Package Distributed on one D.P.

select pkg.PackageID as ‘Pacakge ID’,
pkg.Name as ‘Package Name’,
Pkg.Version as ‘Package Version’,
pkg.PkgSourcePath as ‘Package Path’,
pkg.SourceDate as ‘Pacakge Created Date’,
pkg.SourceSite as ‘Last Update Pacakge’,
pkg.LastRefreshTime,
pkgs.ServerNALPath as ‘Pacakge DP source’,
pkgs.SummaryDate ‘Pacakge Distributed Date’,
pkgs.InstallStatus as ‘Pacakge Distributed status’
from v_Package pkg
inner join v_PackageStatusDistPointsSumm pkgs on pkgs.PackageID= pkg.PackageID
where pkg.PackageType =’8′
and Pkgs.ServerNALPath like ‘%SCCM.vansh.com%’

Below is the output.

Application Distribution Summary

select pkg.PackageID,
pkg.Name,
vpkg.Targeted,
vpkg.Installed,
vpkg.Pending,
vpkg.Retrying,
vpkg.Failed,
vpkg.Unknown,
(vpkg.SourceSize /1024) as ‘Package Size(MB)’,
pkg.PkgSourcePath

from vPkgStatusSummary vpkg
inner join v_Package pkg on pkg.PackageID = vpkg.PkgID
where pkg.PackageType =’8′

Below is the Output

Task Sequence Query

All Task Sequences list in SCCM

Below is the output for Query

Task Sequence Deployment Status count

declare @Total int
select @Total=count(*) from v_ClientAdvertisementStatus
where AdvertisementID=’DEL20005′
select case
when LastState=0 then LastAcceptanceStateName
when LastState=-1 then LastAcceptanceStateName
else LastStateName
end as ‘Status’,
COUNT(*) as Count,
ROUND(100.0*count(*)/@Total,1) as ‘Status%’,
AdvertisementID
from v_ClientAdvertisementStatus
where AdvertisementID=’DEL20005′
group by case
when LastState=0 then LastAcceptanceStateName
when LastState=-1 then LastAcceptanceStateName
else LastStateName
end, AdvertisementID

Task Sequence Deployment details with Hostname

declare @State int
select @State=MessageState
from v_AdvertisementStatusInformation
if @State in (0,1,2,3)
select sys.Netbios_Name0,
sys.Unknown0 as ‘Is Unknown’,
sys.SMBIOS_GUID0 as ‘SMBIOS_GUID’,
ma.MAC_Addresses0,
LastAcceptanceMessageIDName,
LastAcceptanceStatusTime,
site.SMS_Installed_Sites0,
stat.AdvertisementID,
NumFailures = NULL
from v_ClientAdvertisementStatus stat
join v_R_System sys on stat.ResourceID=sys.ResourceID
join v_RA_System_MACAddresses ma on sys.ResourceID=ma.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
where (stat.LastState=0 or stat.LastState=-1) and stat.LastAcceptanceState=@State and stat.AdvertisementID=’DEL20005′
else
select sys.Netbios_Name0,
sys.Unknown0 as ‘Is Unknown’,
sys.SMBIOS_GUID0 as ‘SMBIOS_GUID’,
ma.MAC_Addresses0 as ‘MAC Address’,
stat.LastStatusMessageIDName,
LastStatusTime,
site.SMS_Installed_Sites0 as ‘Installed Site Code’,
stat.AdvertisementID,
NumFailures = (select COUNT(*)
from v_TaskExecutionStatus
where LastStatusMessageID=11135 and AdvertisementID=’DEL20005′ and ResourceID=stat.ResourceID
and ExecutionTime >= (select MAX(ExecutionTime)
from v_TaskExecutionStatus
where LastStatusMessageID=11140 and AdvertisementID=’DEL20005′ and ResourceID=stat.ResourceID
group by AdvertisementID, ResourceID))
from v_ClientAdvertisementStatus stat
join v_R_System sys on stat.ResourceID=sys.ResourceID
join v_RA_System_MACAddresses ma on sys.ResourceID=ma.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
left join (select AdvertisementID, ResourceID, MAX(ExecutionTime) as Time
from v_TaskExecutionStatus
group by AdvertisementID, ResourceID) as tse1
on tse1.AdvertisementID=stat.AdvertisementID and tse1.ResourceID=stat.ResourceID
left join v_TaskExecutionStatus tse2 on tse1.AdvertisementID=tse2.AdvertisementID and tse1.ResourceID=tse2.ResourceID and tse1.Time=tse2.ExecutionTime
where stat.LastState=@State and stat.AdvertisementID=’DEL20005′

Task Sequence Brief details for deployment

with StatAttr as (
select RecordID, IsUnknown=[425], MACAddress=[426], SMBIOS=[427], AdvertID=[401]
from (select * from v_StatMsgAttributes) sma
pivot (min(AttributeValue) for AttributeID in ([425], [426], [427], [401])) pvt
)
SELECT * FROM
(
SELECT
ExecutionTime,
CASE WHEN LastStatusMessageID IN (11139,11140,11141,11142,11143) THEN NULL ELSE Step+1 END as ‘Last Steps’,
CASE WHEN LastStatusMessageID IN (11139,11140,11141,11142,11143) THEN NULL ELSE ActionName END as ‘Last Action’,
CASE WHEN LastStatusMessageID IN (11139,11140,11141,11142,11143) THEN NULL ELSE GroupName END as ‘Last Group’,
LastStatusMessageID,
LastStatusMessageIDName,
ExitCode,
ActionOutput
FROM v_TaskExecutionStatus tse
JOIN v_R_System sys ON tse.ResourceID=sys.ResourceID
JOIN v_RA_System_MACAddresses ma on sys.ResourceID = ma.ResourceID
WHERE AdvertisementID=’DEL20005′ AND ma.MAC_Addresses0=’00:0C:29:2D:17:54′
UNION
SELECT
stat.Time,
NULL,
NULL,
NULL,
stat.MessageID,
info.MessageName,
0,
NULL
FROM v_StatusMessage stat
INNER JOIN StatAttr sa ON stat.RecordID = sa.RecordID
LEFT JOIN v_AdvertisementStatusInformation info ON stat.MessageID = info.MessageID
WHERE (stat.Component = ‘Software Distribution’ or stat.Component = ‘Task Sequence Engine’) AND
sa.MACAddress=’00:0C:29:2D:17:54′ AND sa.AdvertID = ‘DEL20005’
) Merged
ORDER BY Merged.ExecutionTime

Software Installed Details

Get details about Installed Software on Machine

select sys.Name0 as ‘Host Name’,
arp.DisplayName00 as ‘Software Name’,
arp.Publisher00 as ‘Publisher’,
arp.Version00 as ‘Software Version’
from Add_Remove_Programs_64_DATA arp
inner join v_R_System sys on sys.ResourceID=arp.MachineID
inner join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
where fcm.CollectionID =’DEL00027′
and arp.DisplayName00 like ‘%Google Chrome%’
and arp.Version00 = ‘91.0.4472.106’

SCCM Site and Component Details.

Site Component Details

SELECT sys.SiteCode,
sys.ServerName,
sys.RoleName
FROM v_SystemResourceList as sys
left join v_DistributionPointInfo as dp
on sys.NALPath = dp.NALPath
WHERE sys.SiteCode = ‘DEL’
Order by sys.SiteCode, sys.ServerName, sys.RoleName

Site Hierarchy Status

set nocount on
create table #SiteOrder
(
SiteCode char(3),
SiteLevel int,
PreOrder int identity,
PRIMARY KEY (SiteLevel,SiteCode)
)
declare @NextS char(3)
declare @SiteLevel int
set @SiteLevel=1
set @NextS=”
while @NextS is not null
begin
select @NextS=MIN(SiteCode) from v_Site
where ReportingSiteCode=@NextS
and SiteCode not in (select SiteCode from #SiteOrder
where SiteLevel=@SiteLevel)
if @NextS IS NULL
begin
set @SiteLevel=@SiteLevel-1
select @NextS = SiteCode from #SiteOrder where PreOrder=
(select MAX(PreOrder) from #SiteOrder where SiteLevel=@SiteLevel-1)
end
else
begin
insert into #SiteOrder(SiteCode,SiteLevel) values(@NextS,@SiteLevel)
set @SiteLevel = @SiteLevel + 1
end
end
select SPACE(3*(SiteLevel-1))+so.SiteCode as SiteCode,
s.SiteName, s.ServerName,
CASE s.Type WHEN 1 THEN ‘*’ ELSE ‘ ‘ END as ‘Site is a Secondary Site’,
s.ReportingSiteCode,
s.Version, stat.Updated,
CASE stat.Status WHEN 0 THEN ‘*’ ELSE ‘ ‘ END As ‘OK’,
CASE stat.Status WHEN 1 THEN ‘*’ ELSE ‘ ‘ END As ‘Warning’,
CASE stat.Status WHEN 2 THEN ‘*’ ELSE ‘ ‘ END As ‘Error’
from #SiteOrder so
join v_Site s on so.SiteCode=s.SiteCode
join v_SummarizerSiteStatus stat on s.SiteCode=stat.SiteCode
order by so.PreOrder
drop table #SiteOrder