Lookup a system in SCCM and shows last login data
0
/ConfigMgr_RDC/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}
None
efb25c8b-ee4e-4b48-aed0-ae77634aecc9
DataSource1
=Parameters!SUGroup.Value
=Parameters!SUCollection.Value
select
uc.ResourceID
,s.Name0
,'KB' + ui.ArticleID 'KB Number'
,'Check' = (
(case when UI.IsSuperseded = 1 then 'Superseded' else '' end)
+
(case when UI.IsSuperseded + UI.IsExpired = 2 then ' & ' else '' end)
+
(case when UI.IsExpired = 1 then 'Expired' else '' end)
)
,ui.Title
,ui.InfoURL
,stuff ((Select ', ' + UI2.Title
From v_CIRelation_All CR
left join v_UpdateInfo UI1 on UI1.CI_ID = CR.ReferencedCI_ID
left join v_UpdateInfo UI2 on UI2.CI_ID = CR.CI_ID
where RelationType=6
and UI1.IsSuperseded = 1
and UI1.CI_ID = UI.CI_ID
Order by UI2.ArticleID asc
for XML path ('')), 1, 2,'') as 'Superseeded by'
from v_Update_ComplianceStatus UC
inner join vSMS_CIRelation UR on UR.ToCIID = UC.CI_ID
left join v_UpdateInfo UI on UI.CI_ID = UC.CI_ID
left join v_CITargetedMachines TS on TS.CI_ID=UC.CI_ID and TS.ResourceID = UC.ResourceID
join v_CICategories_All vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
join v_CICategories_All cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
left join v_R_System S on S.ResourceID = UC.ResourceID
where
--UR.FromCIID = 17252226
UR.FromCIID = @SUGroup
and UC.Status<>3
and UC.Status=2
and S.ResourceID in (
SELECT FC.ResourceID
FROM v_FullCollectionMembership FC
left join v_Collections C on FC.CollectionID = C.SiteID
--Where C.CollectionName = 'RP - African Alliance Managed Servers'
Where fc.CollectionID = @SUCollection
)
Order by S.Name0, UI.ArticleID
ResourceID
System.Int32
Name0
System.String
KB Number
System.String
Check
System.String
Title
System.String
InfoURL
System.String
Superseeded by
System.String
DataSource1
select c.CollectionName, c.SiteID
from v_Collections c
Where c.CollectionName not like 'All %'
Order by C.CollectionName
CollectionName
System.String
SiteID
System.String
DataSource1
SELECT
AL.CI_ID as ID
,AL.Title as Title
--,*
FROM v_AuthListInfo AL
where AL.CreatedBy <> 'AutoUpdateRuleEngine'
ID
System.Int32
Title
System.String
1.75208in
1.65833in
5.5125in
2.42917in
4.26042in
0.25in
true
true
KB Number
Textbox3
#69be28
2pt
2pt
2pt
2pt
true
true
Super or Exp Check
Textbox5
#69be28
2pt
2pt
2pt
2pt
true
true
Title
Textbox7
#69be28
2pt
2pt
2pt
2pt
true
true
Info URL
Textbox9
#69be28
2pt
2pt
2pt
2pt
true
true
Superseeded by
Textbox11
#69be28
2pt
2pt
2pt
2pt
0.25in
true
true
=Fields!KB_Number.Value
KB_Number
2pt
2pt
2pt
2pt
true
true
=Fields!Check.Value
Check
2pt
2pt
2pt
2pt
true
=Fields!Title.Value
Title
2pt
2pt
2pt
2pt
true
=Fields!InfoURL.Value
InfoURL
=Fields!InfoURL.Value
2pt
2pt
2pt
2pt
true
=Fields!Superseeded_by.Value
Superseeded_by
2pt
2pt
2pt
2pt
1.75208in
true
true
Sytem Name
Textbox28
#69be28
2pt
2pt
2pt
2pt
After
=Fields!Name0.Value
=Fields!Name0.Value
1.75208in
true
true
=Fields!Name0.Value
Name011
2pt
2pt
2pt
2pt
Outstanding_Updates
0.53187in
0.5in
17.36458in
true
true
Windows Updates reported as outstanding as of
=Globals!ExecutionTime
Title
ReportTitle
0.13187in
0.4in
13.10416in
1
2pt
2pt
2pt
2pt
27.0495mm
442.43608mm
14.55208mm
true
true
true
true
Windows Update Status
Textbox13
1.14652mm
47.225mm
11.64167mm
223.91975mm
#69be28
2pt
2pt
2pt
2pt
18.96459mm
true
true
true
true
Please note:
--
Blank means no data availible.
debug info...--
=Parameters!SUGroup.Value
--
=Parameters!SUCollection.Value
--
Textbox35
1.05833mm
131.12725mm
17.90625mm
201.71842mm
2pt
2pt
2pt
2pt
297mm
210mm
20mm
20mm
20mm
20mm
0.13cm
String
Windows Update Group
Deployment_Groups
ID
Title
False
String
SUCollection
Collections
SiteID
CollectionName
Mm
http://zzz/reportserver
93e7e180-6894-4461-9033-f67983546358