Use the v_FullCollectionMembership view with a WHERE statement for the
CollectionID.
Here is our query for our custom deployment states report. I highlighted the
section that handles the collection piece.
declare @__timezoneoffset int
set @__timezoneoffset = DateDiff(HOUR,getutcdate(),getdate())
declare @COLLCOUNT as int
declare @DeploymentLocalID as int
select @DeploymentLocalID = AssignmentID from v_CIAssignment where
Assignment_UniqueID = @DEPLOYMENTID
select @COLLCOUNT=count(*) from v_CIAssignmentTargetedMachines where
AssignmentID=@DeploymentLocalID
select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.Description as DeploymentInfo,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
'Restarts Suppressed' =
CASE a.SuppressReboot
WHEN '0' THEN 'Not Suppressed'
WHEN '1' THEN 'Workstations Only'
WHEN '2' THEN 'Servers Only'
WHEN '3' THEN 'Workstations and Servers'
END,
sn.StateName as LastEnforcementState,
m.Name0 as ComputerName0,
m.User_Name0 as LastLoggedOnUser,
asite.SMS_Assigned_Sites0 as AssignedSite,
DATEADD(hour,@__timezoneoffset,assc.StateTime) as DeploymentStateTime
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_R_System m on m.ResourceType=5 and m.ResourceID=assc.ResourceID and
isnull(m.Obsolete0,0)<>1
left join v_RA_System_SMSAssignedSites asite on m.ResourceID = asite.ResourceID
join v_StateNames sn on assc.StateType = sn.TopicType and
sn.StateID=isnull(assc.StateID,0)
and assc.StateType in (300,301)
where (@DEPLOYMENTID is null or a.Assignment_UniqueID = @DEPLOYMENTID)
AND m.Name0 in (Select Name from v_FullCollectionMembership WHERE
CollectionID=@CollectionID)
group by a.Assignment_UniqueID,a.AssignmentName, a.Description, a.StartTime,
a.EnforcementDeadline, a.SuppressReboot, sn.StateName, sn.TopicType,
sn.StateID, m.Name0, asite.SMS_Assigned_Sites0, m.User_Name0, assc.StateTime
order by sn.StateName
Thanks,
James Massardo
From: [email protected] [mailto:[email protected]] On
Behalf Of Mote, Todd
Sent: Wednesday, July 24, 2013 12:00 PM
To: [email protected]
Subject: [mssms] SSRS SQL query help
I'm trying to scope a canned report about compliance with a deployment,
specifically "Software Updates - C Deployment States\States 4 - Computers in a
specific state for a deployment", to a collection of computers. I'm not sure
how best to do it. I've tried a couple of times without success and thought
that there were some SQL guru's here that might be better at this than me.
Basically I'd like "Computers with state 'Compliant' for 'Windows Server 2008R2
Updates Deployment' that are in the 'All Department Devices' collection " and
ultimately a count too. I'm trying to provide compliance numbers to individual
department heads based on their collections. Any direction anybody can point
me in?
TIA
Todd
NOTICE: This electronic mail message and any files transmitted with it are
intended
exclusively for the individual or entity to which it is addressed. The message,
together with any attachment, may contain confidential and/or privileged
information.
Any unauthorized review, use, printing, saving, copying, disclosure or
distribution
is strictly prohibited. If you have received this message in error, please
immediately advise the sender by reply email and delete all copies.
