query the WSUS sql db

[Bob Findlay]

hi
I would like to search the SQL database that WSUS creates, but I'm having
trouble working out how it all fits together. Is there a published database
schema anywhere?

What I would like to do, is run an SQL query which will return a list of
updates which are required but have not been approved for "all computers".
This list could then be e-mailed so that we don't have to check the status of
the server every day

Any ideas?

Cheers,

Bob

[Steven]

I don't know about a schema, but I've worked a few things together...
based on SQL Profiler traces, and a little old fashioned reverse
engineering that seem to work... :)

http://www.manross.net/wsus/reporting/spSRMCountComputersNeedingUpdates.sql.txt

Here's a similar script (and maybe closer to what you are looking for).

CREATE PROCEDURE [dbo].[spSRMCountUpdatesNeedingApproval]
AS
--unapproved updates (number of)

declare @unapprovedupdates int

SELECT @unapprovedUpdates = COUNT(*)
FROM (SELECT U.UpdateID, U.LocalUpdateID, R.RevisionID
FROM dbo.tbUpdate AS U
INNER JOIN dbo.tbRevision AS R ON
R.LocalUpdateID=U.LocalUpdateID
INNER JOIN dbo.tbProperty AS P ON
P.RevisionID=R.RevisionID
WHERE P.ExplicitlyDeployable=1 AND R.IsLatestRevision=1 AND
U.IsHidden=0
) AS U
WHERE NOT EXISTS (SELECT * FROM dbo.tbDeployment AS D
INNER JOIN dbo.tbRevision AS Re ON
Re.RevisionID=D.RevisionID
WHERE Re.LocalUpdateID=U.LocalUpdateID
AND D.ActionID IN (0,1,2))

if @unapprovedUpdates > 0
BEGIN
declare @msg varchar (400)
declare @sbj varchar (400)
select @msg = 'Please visit the WSUS website to look at and
approve/disapprove outstanding updates.' + char(10) + char(13) +
'http://wsus.somewhere.com/WSUSAdmin'
select @sbj = 'WSUS: There are ' +
convert(varchar(5),@unapprovedUpdates) + ' updates waiting to be
approved'

-- EXEC master.dbo.xp_sendmail @recipients =
'***@somewhere.com',
-- @message = @msg,
-- @subject = @sbj
END
--ENDIF

GO

[Bob Findlay]

Steven
Excellent. thanks v much
Bob

Post by Steven
I don't know about a schema, but I've worked a few things together...
based on SQL Profiler traces, and a little old fashioned reverse
engineering that seem to work... :)
http://www.manross.net/wsus/reporting/spSRMCountComputersNeedingUpdates.sql.txt
Here's a similar script (and maybe closer to what you are looking for).
CREATE PROCEDURE [dbo].[spSRMCountUpdatesNeedingApproval]
AS
--unapproved updates (number of)
FROM (SELECT U.UpdateID, U.LocalUpdateID, R.RevisionID
FROM dbo.tbUpdate AS U
INNER JOIN dbo.tbRevision AS R ON
R.LocalUpdateID=U.LocalUpdateID
INNER JOIN dbo.tbProperty AS P ON
P.RevisionID=R.RevisionID
WHERE P.ExplicitlyDeployable=1 AND R.IsLatestRevision=1 AND
U.IsHidden=0
) AS U
WHERE NOT EXISTS (SELECT * FROM dbo.tbDeployment AS D
INNER JOIN dbo.tbRevision AS Re ON
Re.RevisionID=D.RevisionID
WHERE Re.LocalUpdateID=U.LocalUpdateID
AND D.ActionID IN (0,1,2))
BEGIN
approve/disapprove outstanding updates.' + char(10) + char(13) +
'http://wsus.somewhere.com/WSUSAdmin'
approved'
END
--ENDIF
GO

[Lawrence Garvin (MVP)]

There is no published schema and direct access to the WSUS database schema
is not supported.

Access to the WSUS database data is accomplished via the WSUS API. The SDK
can be downloaded from the WSUS website.

Post by Bob Findlay
hi
I would like to search the SQL database that WSUS creates, but I'm having
trouble working out how it all fits together. Is there a published database
schema anywhere?
What I would like to do, is run an SQL query which will return a list of
updates which are required but have not been approved for "all computers".
This list could then be e-mailed so that we don't have to check the status of
the server every day
Any ideas?
Cheers,
Bob