Client:
List Of Devices in perticular Colelction 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' Query Results:
Check the machine part of which the collections
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 = 'WIN10'
--sys.Name0 in ('WIN10','SCCMPRI')
Query Results:
List the client version count and list 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 Query Results:
List of Operating System Count and list 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 Query Results:
Package Details, Distrubution Status and Deployment Query:
Package Deployment Status with Deployment ID select sys.Name0 as 'Host Name', vc.Name as 'CollectionName', pkg.Name as 'Pacakge Name', sys.User_Name0 as 'User Name', adv.AdvertisementID as 'Deplyment ID', 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 = 'PS120001' order by ads.LastStateName Query Results:
Application Details, Status and Deployment Status Query:
Devices base Application Deployment Status with Deplyment ID
SELECT distinct
vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],
v_CIAssignment.Assignment_UniqueID ,lac.DisplayName AS 'ApplicationName',CollectionName,
IIf([EnforcementState]=1001,'Installation Success',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Installation Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress', IIf([EnforcementState]>=3000 And [EnforcementState]<4000,
'Requirements Not Met ',
IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown', IIf([EnforcementState]>=5000
And [EnforcementState]<6000,'Error','Unknown')))))) AS Status
FROM dbo.v_R_System AS vrs
INNER JOIN (dbo.vAppDeploymentResultsPerClient
INNER JOIN v_CIAssignment
ON dbo.vAppDeploymentResultsPerClient.AssignmentID = v_CIAssignment.AssignmentID)
ON vrs.ResourceID = dbo.vAppDeploymentResultsPerClient.ResourceID
INNER JOIN dbo.fn_ListApplicationCIs(1033) lac
ON lac.ci_id=dbo.vAppDeploymentResultsPerClient.CI_ID
INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws
ON vgws.ResourceID=vrs.resourceid
INNER JOIN v_FullCollectionMembership coll
ON coll.ResourceID = vrs.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos
ON vgos.ResourceID = vrs.ResourceID
WHERE v_CIAssignment.Assignment_UniqueID like '{C6073DF3-DBFD-4F62-BC47-5FE360A03347}'
Query Results:
User & Device based Application Deployment Status with Deplyment ID
select a.UserName,a.MachineName,a.CollectionID,a.CollectionName,lac.DisplayName as'Application Nmae',a.Assignment_UniqueID as 'Deployment ID',a.Technology,
IIf([EnforcementState]=1001,'Installation Success',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Installation Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress', IIf([EnforcementState]>=3000 And [EnforcementState]<4000,
'Requirements Not Met ',
IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown', IIf([EnforcementState]>=5000
And [EnforcementState]<6000,'Error','Unknown')))))) AS Status
from vAppDeploymentAssetData a
INNER JOIN dbo.fn_ListApplicationCIs(1033) lac ON lac.ci_id=a.AppCI
where a.Assignment_UniqueID like '%{83B0F9D5-3436-49DB-8025-0E6775FF41B0}%'
order by a.UserName
Query Results:
Software Update deployment Status and patch scan staus (Updates Required,Not required,Installed & Unknown):
Sofware Update Deployment Status with Deplyment ID
select vrs.Name0,vrs.Active0,
--vrs.AD_Site_Name0,vrs.User_Name0,vrs.Operating_System_Name_and0,
a.Assignment_UniqueID as DeploymentID,C.CollectionID,c.Name 'Collection Name',
a.AssignmentName as DeploymentName,a.StartTime as Available,a.EnforcementDeadline as Deadline,sn.StateName as LastEnforcementState,
wsus.LastErrorCode as 'LasErrorCode',wsus.LastScanTime as 'LastWSUSScan',DateDiff(D, wsus.LastScanTime, GetDate()) as 'LastWSUSScan Age',
wks.LastHWScan,
DateDiff(D, wks.LastHwScan, GetDate()) as 'LastHWScan Age'
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_R_System vrs on vrs.ResourceID = assc.ResourceID
join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID
join v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID
LEFT JOIN v_Collection C ON C.CollectionID = a.CollectionID
where a.Assignment_UniqueID like '{9846A1A8-3289-447C-9CCF-EA41C48EB916}'
and assc.StateType in (300,301)
order by 11 desc
Query Results:
Enforcement states for a software updates deployment
select
vrs.name0 as ComputerName,
a.AssignmentName as DeploymentName,
assc.StateTime ,
a.CollectionName, sn.StateName as LastEnforcementState
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_R_System vrs on vrs.ResourceID=assc.ResourceID
where
--a.AssignmentID='xxxxxxxx' or
a.Assignment_UniqueID like '{9846A1A8-3289-447C-9CCF-EA41C48EB916}'
order by LastEnforcementState
Query Results:
Software updates Instllation Status
select rsys.name0 AS 'Computername',
os.Caption0 AS 'Operating System',
--os.BuildNumber0 AS 'Build Number',
ui.ArticleID AS 'Update ID',
ui.Title,
--ui.Description,
--Set Install Status
Case
when ucs.Status='0' then 'Detection state unknown'
When ucs.Status='1' then 'Update is not required'
when ucs.Status='2' then 'Update is required'
When ucs.Status='3' then 'Update is installed'
End As 'Install Status',
--Set client Activity
Case
When ch.ClientActiveStatus='1' then 'Active'
When ch.ClientActiveStatus='0' then 'Inactive'
End As 'Client Active Status',
ui.InfoURL,
ui.DatePosted
from v_updateinfo ui
join v_Update_ComplianceStatusAll ucs on ucs.ci_id=ui.ci_id
join v_CICategories_All catall on catall.CI_ID=UCS.CI_ID
join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='UpdateClassification'
join v_R_System rsys on rsys.resourceid=ucs.resourceid
join v_GS_OPERATING_SYSTEM os on os.ResourceID = ucs.ResourceID
join v_CH_ClientSummary ch on ch.resourceID = ucs.ResourceID
where rsys.name0 in ('ORG5405DBA9F104')
--and ucs.Status in ('0','1','2','3')
order by ui.Title
Query Results:
0 $type={blogger}:
Post a Comment