-2

iam using SCCM , iam running a query to get data from SCCM database. there is deployment and paRticular collection , after running i get count of servers which are available in progress and other. please find query

    Declare @SoftwareUpdateGroupName as varchar(255)

Set @SoftwareUpdateGroupName = 'RDP-Vulnerabilities-Windows7' 
Select
 Li.Title,
Ds.CollectionName as 'CollectionName',
Ds.CollectionID,

Ds.NumberTotal as 'Target',

(Ds.NumberTotal - Ds.NumberUnknown) as 'Available Target',

Ds.NumberSuccess as 'Migrated',

Ds.NumberErrors as 'Error',

Ds.NumberInProgress as 'In Progress',

Ds.NumberOther as 'Remaining',

Ds.NumberUnknown as 'Not Available',


case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0'
 Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) 
 End as '% Success'

 from v_DeploymentSummary Ds

left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID

left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID
 --left join v_CH_ClientSummary 

Where Ds.FeatureType = 5

and Li.Title like @SoftwareUpdateGroupName and
 Ds.CollectionName like '%W10 - Ring 2 - Wave 2%'

order by Ds.CollectionName 

--select * from fn_AppDeploymentAssetDetails(1033) where collectionid like '%CAS02E0B%'

output iam getting is like this enter image description here i need to further drill down non available servers, is there any way things i did 1) go through tables v_client_summary but doesnt help 2) went through SCCM report in SCCM Management 4 - Deployments that target a collection its giving all other status except enter image description here

way its shown bifurcation in dashboard , i need same from query but its not giving me desired result.

deepti
  • 729
  • 4
  • 17
  • 38

2 Answers2

0

As far as I understand a software update deployment has no deployment status per target client. To evaluate the "status" per client you need to evaluate the compliance state based on next software update scan data.

Marcel
  • 1
-1

You just need to add below in your where clause. Add condition in your query

`.... where.... 
     Ds.NumberUnknown IS NOT NULL;`
Himanshu
  • 3,830
  • 2
  • 10
  • 29
  • how that will solve, i need check client passed/Active in Available and remaining all in not available. so from 666 i need 110 in remaining column and other in not available – deepti Dec 03 '19 at 13:07
  • Yeah above is totally wrong. The thing is I havent got your query now coz the data which you are showing through query is totally different from expected output as client status etc – Himanshu Dec 03 '19 at 13:19
  • query gives this output which i have shown in pic, how come its wrong. – deepti Dec 03 '19 at 13:30
  • I mean my query is wrong. Provide more apt info as your expected output is totally different from the columns output you are showing up – Himanshu Dec 03 '19 at 13:54