Hello,
one question again about the reports. in the moment i use the following which displays me the compliance report of all systems:
SQL:
Select Deploymentname, Available, Deadline,
cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) as varchar(256)) + '%' AS '% Compliant',
[Compliant],
[Enforcement state unknown],
[successfully installed update(s)],
[Failed to install update(s)],
[installing update(s)],
[Waiting for another installation to complete],
[Pending system restart],
[Downloading update(s)]
From
(select
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers
from v_CIAssignment a
join v_AssignmentState_Combined assc
on a.AssignmentID=assc.AssignmentID
join v_StateNames sn
on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,
sn.StateName) as PivotData
PIVOT
(
SUM (NumberOfComputers)
FOR LastEnforcementState IN
( [Compliant],
[Enforcement state unknown],
[successfully installed update(s)],
[Failed to install update(s)],
[installing update(s)],
[Waiting for another installation to complete],
[Pending system restart],
[Downloading update(s)])
) AS pvt
What i now want is to show me the status of all updates in the updatelists call Windows Server 2003, Windows Servern 2008 and Windows Server 2008R2. Is this possible and if yes can someone help me please?
PS: i didnt want to type in something manually instead all should be displayed without any popup if possible
Thanks a lot.
Thorsten
Edit:
Hello again, here i have an new report but still problems:
set nocount on
declare @FromDate datetime; set @FromDate=convert(datetime, @Year+'-'+case when @MonthNumber<>'' then @MonthNumber else '01' end+'-01', 20)
declare @ToDate datetime; set @ToDate=dateadd(month, case when @MonthNumber<>'' then 1 else 12 end, @FromDate)
declare @VendorID int; if @Vendor='Microsoft' select @VendorID=Microsoft from v_CategoryInfo where CategoryTypeName='Microsoft' and CategoryInstanceName='Microsoft'
declare @ProductID int; if @Product='Windows Server 2003' set @ProductID=0 else select @ProductID=CategoryInstanceID from v_CategoryInfo where CategoryTypeName='Windows Server 2003' and CategoryInstanceName=@Product
declare @ClassID int; if @UpdateClass='' set @ClassID=0 else select @ClassID=CategoryInstanceID from v_CategoryInfo where CategoryTypeName='UpdateClassification' and CategoryInstanceName=@UpdateClass
declare @CI table(CI_ID int primary key)
insert @CI(CI_ID)
select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0 and ci.DateRevised>=@FromDate and ci.DateRevised<@ToDate
and (@VendorID=Microsoft or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID='Microsoft'))
and (@ProductID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@ProductID))
and (@ClassID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@ClassID))
select
Vendor=ven.CategoryInstanceName,
UpdateClassification=cls.CategoryInstanceName,
ArticleID,
BulletinID,
Title,
Approved=case when exists(select 1 from v_CITargetedCollections where CI_ID=ci.CI_ID and CollectionID='SMS000FS) then '*' else '' end,
Present=NumPresent,
Missing=NumMissing,
NotApplicable=NumNotApplicable,
Unknown=NumUnknown,
Total=NumTotal,
PCompliant=convert(numeric(5,2), isnull((NumPresent+NumNotApplicable)*100.0/nullif(NumTotal, 0), 100)),
PNotCompliant=convert(numeric(5,2), isnull((NumMissing)*100.0/nullif(NumTotal, 0), 0)),
PUnknown=convert(numeric(5,2), isnull((NumUnknown)*100.0/nullif(NumTotal, 0), 0)),
CollectionID='SMS000FS',
UniqueUpdateID=CI_UniqueID,
InformationURL=InfoURL
from @CI ci
left join v_UpdateSummaryPerCollection cs on cs.CI_ID=ci.CI_ID and cs.CollectionID='SMS000FS'
left join v_UpdateInfo ui on ui.CI_ID=ci.CI_ID
left join v_CICategoryInfo_All ven on ven.CI_ID=ci.CI_ID and ven.CategoryTypeName='Company'
left join v_CICategoryInfo_All cls on cls.CI_ID=ci.CI_ID and cls.CategoryTypeName='UpdateClassification'
order by 1, 2, 3
i want that the Collection ID, the Vendor and the product are filled automatically with an fixed data.
si this possible?
