{"id":25302,"date":"2020-08-17T14:47:19","date_gmt":"2020-08-17T14:47:19","guid":{"rendered":"http:\/\/endusersupports.com\/?page_id=25302"},"modified":"2021-07-30T08:20:12","modified_gmt":"2021-07-30T08:20:12","slug":"sql-query","status":"publish","type":"page","link":"https:\/\/endusersupports.com\/index.php\/sql-query\/","title":{"rendered":"SQL Query"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

<\/p>\n

Here is a list of SQL queries to assist with common reporting tasks<\/strong><\/span><\/p>\n

<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

SQL Query About Clients.<\/span><\/strong><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tMachine with Specific Colletion<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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’<\/p>\n

Below is the output.<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tTo Check machine part of which collection<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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 sys.Name0 = ‘SCCM’<\/p>\n

Below is the output.<\/p>\n

\"\"<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tList of Client Version Count and list<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

To get the client counts<\/p>\n

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<\/p>\n

Below is the output.<\/p>\n

\"\"<\/p>\n

Get the Machine List.<\/p>\n

select sys.Name0,
sys.Client_Version0 as ‘Client Version’
from v_R_System sys<\/p>\n

Below is the output.<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tList of Operating System Count and list<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

To Get the Operating System Count<\/p>\n

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<\/p>\n

Below is the output<\/p>\n

\"\"<\/p>\n

To get machine list.<\/p>\n

select sys.Name0,
sys.Operating_System_Name_and0 as ‘O.S. Name’
from v_R_System sys<\/p>\n

Below is the output<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tClient Health Status <\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

Below is the output.<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tClient Deployed Status<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tClient Assignment Failure Status<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tDuplicate MAC Address<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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)<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

\u00a0<\/p>\n

Software Update(Patches) Compliance Required, Missing and Deployment Status Query<\/span><\/strong><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tTo Check Patch Status (Installed, Required, Unknown) count per Machine<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

Bellow is the query to get the Patch details like Installed, Missing, Not Required and Unknows count per machine.<\/p>\n

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 \u00a0Then 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’
\u00a0and AL.Title = ‘June_Month_Patch_Ddeployment_Servers’
group by vr.Netbios_Name0<\/p>\n

Below is the output for the query<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPatches status per Machine (How many patches Installed or Missing))<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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 \u00a0Then ‘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’<\/p>\n

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<\/p>\n

\u00a0<\/p>\n

Below is the query output.<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tOne Patches Status on Machines<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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 \u00a0Then ‘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’<\/p>\n

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’
and upd.DisplayName like ‘%KB4570505%’
order by vr.Name0<\/p>\n

Below is output for Query<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tOn one Machine Patches Status<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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 \u00a0Then ‘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’<\/p>\n

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’
and vr.Name0 = ‘SCCM’
order by vr.Name0<\/p>\n

Below Is the Output for Query<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tOver All Patches Compliance Count and Percentage<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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 %’<\/p>\n

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<\/p>\n

where LI.Title = ‘June_Month_Patch_Ddeployment_Servers’
and coll.CollectionID = ‘DEL0002E’<\/p>\n

Below is the Output for Query<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPatches Deployment Compliance (Active Deployment)<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

Here is output for the query<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

\u00a0<\/span><\/p>\n

Package Details, Status and Deployment Query<\/span><\/strong><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPackage Deployment Status with Deployment ID<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

Below is the output<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPackage Deployment State<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

Below is the output<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPackage Deployment Status<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

Below is the output<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tAll Packages available In SCCM Console<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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’<\/p>\n

Below is the Query Output<\/p>\n

\"\"<\/p>\n

\u00a0<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPackage Distributed Summary to D.P.'s<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

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’<\/p>\n

\u00a0<\/p>\n

Below is the output for the query<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPackages Distribution Status on D.P.<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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%’<\/p>\n

Below is the output\u00a0<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPackage not Distributed to any D.P.<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

Below is the output<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

\u00a0<\/span><\/p>\n

Application Details, Status and Deployment Status Query<\/span><\/strong><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tApplication Deployment Status (Specific Application)<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

SELECT ApplicationName, CollectionID, AssignmentID, ParentID, name as ‘Application Status’, count as ‘Status Count’<\/p>\n

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<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tAll Application Deployment Status<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t


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<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tAll Applications Available in SCCM Console<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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 =’8′<\/p>\n

Below is the Output<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tAll Application Status Distributed on DP,s<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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′<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tPackage Distributed on one D.P.<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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%’<\/p>\n

Below is the output.<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tApplication Distribution Summary <\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

from vPkgStatusSummary vpkg
inner join v_Package pkg on pkg.PackageID = vpkg.PkgID
where pkg.PackageType =’8′<\/p>\n

\u00a0<\/p>\n

Below is the Output<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

\u00a0<\/span><\/p>\n

Task Sequence Query<\/span><\/strong><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tAll Task Sequences list in SCCM<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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 =’4′<\/p>\n

\u00a0<\/p>\n

Below is the output for Query<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tTask Sequence Deployment Status count<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

\u00a0<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tTask Sequence Deployment details with Hostname<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

\u00a0declare @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′<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tTask Sequence Brief details for deployment<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

Software Installed Details<\/span><\/strong><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tGet details about Installed Software on Machine<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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’<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

SCCM Site and Component Details.<\/span><\/strong><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tSite Component Details<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

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<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t<\/i><\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tSite Hierarchy Status<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t


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<\/p>\n

\"\"<\/p><\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

Here is a list of SQL queries to assist with common reporting tasks SQL Query About Clients. Machine with Specific Colletion 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 sysinner join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceIDinner join v_Collection vc on vc.CollectionID = fcm.CollectionIDwhere fcm.CollectionID = ‘SMS00001’ […]<\/p>\n","protected":false},"author":1,"featured_media":25260,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"elementor_theme","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"class_list":["post-25302","page","type-page","status-publish","has-post-thumbnail","hentry"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/pages\/25302","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/comments?post=25302"}],"version-history":[{"count":452,"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/pages\/25302\/revisions"}],"predecessor-version":[{"id":28725,"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/pages\/25302\/revisions\/28725"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/media\/25260"}],"wp:attachment":[{"href":"https:\/\/endusersupports.com\/index.php\/wp-json\/wp\/v2\/media?parent=25302"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}