Hi ALL

Im trying to write a new sql query to generate a sccm report based on deployment id and machine id or host name.. the report is as follows..

im getting some data after 5min in sql db that too multiple results, but not in sccm reporting.. please help me..

main query:

declare @DeploymentLocalID as int
declare @RscID as int

select @DeploymentLocalID=AssignmentID from v_CIAssignment where Assignment_UniqueID=@DEPLOYMENTID and UpdateAssignment=1

select Assignment_UniqueID as DeploymentID,
AssignmentName as DeploymentName
from v_CIAssignment where AssignmentID=@DeploymentLocalID

select @RscID=ResourceID from v_R_System where ((Name0 = @MachineName) and (Active0 = 1));

select catinfo.CategoryInstanceName as Vendor0,
      ArticleID as ArticleID,
      BulletinID as BulletinID,
      Title as Title,
      Approved = case when col.CollectionID is not null then '*' else '' end,    
      NumPresent as Installed,
      NumMissing as Required,
      NumNotApplicable as NotRequired, 
      NumUnknown as Unknown,
      NumTotal as Total,
      PCompliant=case when NumTotal<>0 then CAST((NumPresent+NumNotApplicable)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
      PNotCompliant=case when NumTotal<>0 then CAST((NumMissing)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
      PUnknown=case when NumTotal<>0 then CAST((NumUnknown)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
  col.CollectionID,
   UniqueUpdateID=ui.CI_UniqueID,
   ctm.ResourceID
from v_CIAssignmentToCI cia
join  v_UpdateInfo ui on cia.CI_ID = ui.CI_ID
join v_UpdateComplianceStatus css on ui.CI_ID=css.CI_ID
join (v_CICategories_All catall join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company')
      on catall.CI_ID=ui.CI_ID
left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID --and col.CollectionID=@CollID
join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID --and us.CollectionID=@CollID
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 cia.AssignmentID=@DeploymentLocalID AND css.ResourceID = @RscID

prompts:

deployment id:

begin
 if (@__filterwildcard = '')
select
cia.Assignment_UniqueID as DeploymentID , cia.AssignmentName as DeploymentName
from v_CIAssignment cia
where cia.UpdateAssignment=1
order by AssignmentName
else
select
cia.Assignment_UniqueID as DeploymentID , cia.AssignmentName as DeploymentName
from v_CIAssignment cia
where cia.UpdateAssignment=1
and Assignment_UniqueID like @__filterwildcard or AssignmentName like @__filterwildcard
order by AssignmentName
end

machine id:

begin
 if (@__filterwildcard = '')
  select distinct Name0 from v_R_System WHERE isnull(Obsolete0,0)<>1 order by Name0
 else
  select distinct Name0 from v_R_System
  WHERE Name0 like @__filterwildcard
 and isnull(Obsolete0,0)<>1 order by Name0
end

Your query is a complex query as it be tough for other to review it.

Secondly, the reason why your query is take so long is that it is not looking at just 1 PC it is look at all of the PCs and giving your result for all PCs in environment.

A number of these columns dont make sense when you think this is should be for one PC only, Total will be 1 or 0 when this query is looking at one PC. PCompliant, PCompliant and PUnknown will be 100% or 0%

hii

My requirement is

One report to show the compliance for all of the patches in the system for one computer. The GUI entry would have only 1 for computer host name. This report would then show each patch and the status for that computer.

hii

My requirement is

One report to show the compliance for all of the patches in the system for one computer. The GUI entry would have only 1 for computer host name. This report would then show each patch and the status for that computer.

What is wrong with the built-in computer detail SU report?

http://social.technet.microsoft.com/Forums/systemcenter/en-US/ae850a4f-e819-4831-9d75-a6439b1910d1/patch-compliance-report-to-triage-patching?forum=configmgrreporting#c335631c-9b2e-4604-9bde-960aeaebd0e1

Look into the above thread the query will tell you the required and installed details for a machine

if you want the count for each servers then you can do the pivot table ...

declare @CI_ID int;
select @CI_ID=AssignmentID from v_CIAssignment where AssignmentName = @Assignment;

select rsys.Name0, SUM(CASE bigpoo.Status WHEN 3 THEN 1 ELSE 0 END) AS [Installed Count], SUM(CASE bigpoo.Status WHEN 2 THEN 1 ELSE 0 END) AS [Missing], SUM(CASE bigpoo.Status WHEN 0 THEN 1 ELSE 0 END) AS [Not applicable], SUM(CASE bigpoo.Status WHEN 1 THEN 1 ELSE 0 END) AS [Unknown]
from (Select CI_ID, ResourceID, Status from v_UpdateComplianceStatus 
union select CI_ID, ResourceID, 0 as [Status] from v_UpdateComplianceStatus_NotApplicable as UPNA
union select CI_ID, ResourceID, 1 as [Status] from v_UpdateComplianceStatus_Unknown as UPU) as BIGPOO inner join
v_UpdateInfo as ui on BIGPOO.CI_ID = ui.CI_ID inner join 
v_CIAssignmentToCI as CItoCI on CItoCI.CI_ID = BIGPOO.CI_ID inner join
v_CIAssignment as CIA on CIA.AssignmentID = CItoCI.AssignmentID inner join
v_R_System as RSYS on RSYS.ResourceID = BIGPOO.ResourceID
where ui.IsExpired = 0 and cia.AssignmentID =@CI_ID and rsys.ResourceID 
in (Select ResourceID from v_CIAssignmentTargetedMachines where v_CIAssignmentTargetedMachines.AssignmentID = @CI_ID)
group by RSYS.Name0

Create a report with the above query you will get what you wanted in the report  its deployment management based report in detail for each machines.

@Kamala I dont understand how the query that you post meet the requirement that technetsniper post?

@Technetsniper, What is wrong with Compliance 6 - Specific computer, it lists all SU that are applicable to the PC?