Ok, the query editor is pretty good at getting information out of the Flarepath WUA database but it is never going to meet every eventuality. I had a couple of questions recently about how to get information from the SUS Approval logs.
The first was how to get a list of items that have not been approved or unapproved and also how long they have stayed like this. I generated two new queries (one to provide the information and another to show the approval history for a particular item) which will be included in an upcoming release.
There are two tables that record the SUS Approval information. The first is called ApprovedEntries and records each instance of an approval list. The second is called ApprovedItems and records the items contained in the approval lists. They are related by a foreign key in the ApprovedItems table (ApprovedEntryID).
The first query is relatively straight forward and just takes a basic join from the ApprovedItems and ApprovedEntries tables. I won’t go into detail about this query.
The second query is much more complex and uses two temporary tables and a cursor.
The first step is to get the last approval list. Flarepath WUA records this under the ApprovedEntries table. All the items that are on this approval list are stored in the ApprovedItems table and have a matching ApprovedEntryID field:
DECLARE @last uniqueidentifier
SELECT
TOP 1
@last = ApprovedEntryID
FROM
ApprovedEntries
ORDER BY [Time]
DESC
This code just returns the newest ID and assigns it to a variable.
We need to create two temporary tables to hold information:
CREATE TABLE #SUSTemp
(
title nvarchar(200),
)
CREATE TABLE #SUSTemp2
(
Title nvarchar(200),
[Time]
datetime,
Computer nvarchar(200)
)
We then need to select all the rows from the ApprovedItems table that have a matching ApprovedEntryID field are currently unapproved:
INSERT INTO #SUSTemp
(title)
SELECT
ApprovedItems.Title
FROM
ApprovedItems
INNER JOIN
ApprovedEntries
ON
ApprovedItems.ApprovedEntryID =
ApprovedEntries.ApprovedEntryID
INNER JOIN
SUSServers
ON
ApprovedEntries.SUSServerID = SUSServers.SUSServerID
INNER JOIN
Computers
ON
SUSServers.ServerID = Computers.ComputerID
WHERE
(ApprovedItems.Status = N'unapproved')
AND
(ApprovedItems.ApprovedEntryID = @last)
This is why we need the temporary table.
Once we have a list of all the titles (eg XP Service Pack 2) we then need to find when it first appeared in the SUS ApprovedItems table. To do this we have to use a Cursor. The problem with this cursor is that it will return a results set for each item it finds and although the Query Editor can cope with this, I wanted it to be returned as one set of results. This is what the seconds table is for, to take the results of the cursor:
-- Use a cursor to iterate through this list...
DECLARE @title nvarchar(200)
DECLARE title_cursor CURSOR
FOR
SELECT title FROM #SUSTemp
OPEN title_cursor
FETCH NEXT FROM
title_cursor
INTO @title
WHILE @@FETCH_STATUS = 0
BEGIN
--
...to find the id of when it first appeared in the SUS approval list
DECLARE
@id uniqueidentifier
SELECT TOP 1
@id = ApprovedItems.ApprovedItemID
FROM
ApprovedItems
INNER JOIN
ApprovedEntries
ON
ApprovedItems.ApprovedEntryID = ApprovedEntries.ApprovedEntryID
WHERE
(ApprovedItems.Title = @title)
ORDER BY
ApprovedEntries.[Time]
-- Insert the information about the item into the second temporary table
INSERT INTO #SUSTemp2
(
Title,
[Time],
Computer
)
SELECT
ApprovedItems.Title,
ApprovedEntries.[Time],
Computers.DNSHostName
FROM
Computers
INNER JOIN
SUSServers
ON
Computers.ComputerID = SUSServers.ServerID
INNER JOIN
ApprovedItems
INNER JOIN
ApprovedEntries
ON
ApprovedItems.ApprovedEntryID = ApprovedEntries.ApprovedEntryID
ON
SUSServers.SUSServerID = ApprovedEntries.SUSServerID
WHERE
(ApprovedItems.ApprovedItemID = @id)
FETCH NEXT FROM title_cursor
INTO @title
END
CLOSE title_cursor
DEALLOCATE title_cursor
Once we have the results in the temporary table, we just return the
results back to the query editor and clean up:
-- Return the calculated
results
SELECT
Title,
DATEDIFF(d, [Time], GETDATE()) AS [Days
Unapproved],
[Time] AS Downloaded,
Computer AS [SUS Server]
FROM
#SUSTemp2
ORDER BY
DATEDIFF(d, [Time], GETDATE()) DESC, Title
-- Clean up
DROP TABLE #SUSTemp
DROP TABLE #SUSTemp2
NOTE: New updates will have a status of "none".
Now because this is a special query, we have to tell the Query Editor to handle it differently. We set the "use base SQL" option to on in the Advanced Options of the query and then add a special "Placeholder" table. This allows us to then add as many fields as there are columns in the results and name them to whatever we want.
The next questions were beyond the scope of the Query Editor but could be executed through SQL Query Analyser, SQL Enterprise Manager, OSQL or even Access/Excel. Again, I created two queries to answer these.
The first query will return a list of new updates between two given dates:
SELECT
ApprovedItems.Title
FROM
ApprovedItems INNER
JOIN
ApprovedEntries ON ApprovedItems.ApprovedEntryID =
ApprovedEntries.ApprovedEntryID
WHERE
(ApprovedItems.Status = N'none')
GROUP BY
ApprovedItems.Title
HAVING
(MIN(ApprovedEntries.[Time]) >= CONVERT(DATETIME, '2005-01-08 00:00:00',
102)) AND (MIN(ApprovedEntries.[Time]) <= CONVERT(DATETIME, '2005-01-15 00:00:00', 102))
As you can see, when SUS downloads new updates it assigns a status of “none” (this does not apply to automatically approved re-issued updates). All this query does is to pull a list of items that first appeared during the two dates.
The second is a bit more complex and uses a sub-query to check to see if the update does not currently have an approval status:
SELECT
ApprovedItems.Title
FROM
ApprovedItems INNER
JOIN
ApprovedEntries ON ApprovedItems.ApprovedEntryID =
ApprovedEntries.ApprovedEntryID
WHERE
(ApprovedItems.Status = N'none') AND ApprovedItems.Title NOT IN
(
SELECT
ApprovedItems.Title
FROM
ApprovedItems INNER JOIN
ApprovedEntries
ON ApprovedItems.ApprovedEntryID =
ApprovedEntries.ApprovedEntryID
WHERE
(ApprovedItems.Status <> N'none')
GROUP BY
ApprovedItems.Title
)
GROUP BY
ApprovedItems.Title
HAVING
(MIN(ApprovedEntries.[Time]) >= CONVERT(DATETIME, '2004-10-08 00:00:00',
102)) AND (MIN(ApprovedEntries.[Time]) <= CONVERT(DATETIME, '2004-10-14 00:00:00', 102)) ORDER BY ApprovedItems.Title
If you would like to know how long these items have remained unapproved then simply add the following code to the first SELECT statement:
DATEDIFF(d, MIN(ApprovedEntries.[Time]), GETDATE()) AS
Age
I hope this has given you a little insight into the data collected by Flarepath WUA!
