I need to add a dataset to this report that would let the user select when the
updates were released. I want to have 3 options in the dropdown in the new
dataset "On or Before Today", "More than 30 days Ago" and "More than 60 days
ago".... How could that be done?
select distinct
fcm.Name as 'Computer Name',
Count(*) as 'Count'
from
(SELECT param FROM ES_SplitList(@Classification, ',')) Class,
dbo.v_Collection Col
join dbo.v_FullCollectionMembership FCM on Col.CollectionID = FCM.CollectionID
join dbo.v_UpdateComplianceStatus UCS on UCS.ResourceID = FCM.ResourceID
join fn_ListUpdateCIs(1033) LUCI on UCS.CI_ID = LUCI.CI_ID
join dbo.v_CICategories_All catall2 on catall2.CI_ID = UCS.CI_ID
join dbo.v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID =
catinfo2.CategoryInstance_UniqueID and
catinfo2.CategoryTypeName='UpdateClassification'
Left join dbo.v_CITargetedMachines CTM on CTM.CI_ID = UCS.CI_ID and
CTM.ResourceID = FCM.ResourceID
Where
Col.Name = @Coll
and UCS.Status = 2
and (
((ctm.ResourceID is not null) and (@Deployed = 'Yes'))
or ((isnull(ctm.ResourceID,0) = 0) and (@Deployed = 'No'))
or (@Deployed = 'Both') )
and (
(catinfo2.CategoryInstanceName = 'Critical Updates' and Class.param
='Critical Updates')
or (catinfo2.CategoryInstanceName = 'Security Updates' and Class.param =
'Security Updates')
or (catinfo2.CategoryInstanceName = 'Definition Updates' and Class.param
= 'Definition Updates')
or (catinfo2.CategoryInstanceName = 'Service Packs' and Class.param =
'Service Packs')
or (catinfo2.CategoryInstanceName = 'Update Rollups' and Class.param = 'Update
Rollups')
or (catinfo2.CategoryInstanceName = 'Tools' and Class.param = 'Tools')
or (catinfo2.CategoryInstanceName = 'Feature Packs' and Class.param =
'Feature Packs')
or (catinfo2.CategoryInstanceName = 'Updates' and Class.param = 'Updates')
)
and LUCI.Articleid is not Null
Group by
fcm.Name
Order by
2 desc
________________________________
John Marcum
MCITP, MCTS, MCSA
Desktop Architect
Bradley Arant Boult Cummings LLP
________________________________
[H_Logo]
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
