Flarepath Blog

Patch Experience Database

2005-05-16T09:58:00.000+01:00

For those of you that have been to our site, you may have noticed a link called "Patch DB". This free community area is for people to share knowledge or experience deploying patches. We hope to get every IT or Patch manager to check this site before they deploy a patch.

We need contributors though - the database will only be as good as the information it contains. Anyone can view and search the database and there are three ways to search: by OS, by Patch/KB/Q/Article number of good old free text.

On top of that, there is a pda version and we are also working on a web service that application developers can use to integrate into their applications.

To post to the database, we do ask that you have a Flarepath member account. This is so that we can track the posts and provide a facility for you to edit them at a later stage. Member access to our web site is of course free.

So get over to http://www.flarepath.com/patchdb and start contributing.

2003 Service Pack 1 and SQL Server Reporting Services

2005-04-06T12:28:00.000+01:00

After applying Service Pack 1 for Windows Server 2003, we found that the Reports folder on our SQL Server Reporting Services web site would return a 401: Unauthorised error. We knew that everything was working ok before hand so it must have been a security change in the Service Pack that caused the problem. We reported this issue to Microsoft and sent through several log files.

As usual, Microsoft’s technical support was first class and we had a solution the next day. It turns out that Service Pack 1 has an update which is designed to detect and prevent “man-in-the-middle” attacks. There were two suggested fixes for this. The first was to run the Reports and ReportsServer virtual directories under the Default App Pool. We tried this and it didn’t work for us.

The second was to add a DWORD entry called DisableLoopbackCheck to HKLM\System\CurrentControlSet\Control\Lsa. Setting this key to a value of 1 fixed our issue.

Our concern was that this would leave us open to attack but we decided that this attack vector was pretty slim (for us).

Batch File to Install the Agent on XP SP2

2005-03-16T23:54:00.000Z

A customer asked me if they should install the agent on all computers or only on specific ones. You usually only need the agent if the computer falls into one of the following groups:

The Windows Update client is version 5 (XP SP2, Server 2003 SP 1 or WUS clients).
It is behind a firewall
DCOM/RPC or File & Print have been disabled
It’s a laptop and only occasionally connects to the LAN

He has a mix of 2000 and XP SP 2 machines and wondered how to get the agent onto the machines so we came up with the following batch file which could be used as part of a logon script. I must admit that it’s been many years since I looked at good old batch files and it felt kind of good re-learning some of the commands :)

Here is the batch file:

@ECHO OFF
:CHECKXP
SET XP=0
REG.EXE QUERY "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion" /v ProductName FIND/I "Microsoft Windows XP" && SET XP=1
IF %XP% == 1 GOTO CHECKSP2
GOTO END

:CHECKSP2
SET SP=0
REG.EXE QUERY "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion" /v CSDVersion FIND/I "Service Pack 2" && SET SP=2
IF %SP% == 2 GOTO CHECKAGENT
GOTO END

:CHECKAGENT
IF NOT EXIST "C:\Program Files\Flarepath Windows Update Analyser\Agent\FWUAAgent.exe" GOTO INSTALL
GOTO END

:INSTALL
\\Brazil\FWUA$\Setup.exe /s /v/qn

:END
ECHO Flarepath WUA Agent check complete.
PAUSE

You will have to change the location of the installation point to match yours and don't forget to configure the agent connection settings in Flarepath WUA Tools Options Agents prior to deploying the Agent.

Going beyond the Query Editor - SUS Approval Logs

2005-03-09T12:41:00.000Z

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!

Articles

2005-02-28T14:10:00.000Z

The great thing about System Administrators on staff is being able to speculate about what we think people want to see – especially in our articles. Over the course of the next couple of weeks, we will be adding more articles demonstrating the features of Flarepath WUA.

If though, you would like to see something or have a specific request please add to the comments and we will try to get it written up.

Test, test and then think up some unimaginable scenarios

2005-02-27T18:47:00.000Z

Well, just the next day after releasing 1.2 we had a report of an issue with the Query Editor showing live information. Upon testing we replicated the issue and a patch has been created and is currently undergoing testing. This problem is to do with IIS recording the date and time in UTC and we was something we really should have picked up on.

Anyway, this got us thinking a bit more about regional information and although we develop and test in three regions (US, UK and Russia) we don’t actually work in different time zones. All the servers and development machines are at the Head Office in the UK and everyone remote desktops in.

With 1.2, Flarepath WUA and SQL Server have to be in the same time zone for the real time queries to work properly. This is just something that we will have to live with for now but we will certainly take this into account during the development and testing of version 2.0!

The scenarios may be unimaginable to us but you can bet that there are many people out there with exactly this type of setup. If you’re one of them, drop us a line and we will work on a fix for you.

WUA 2.0

2005-02-26T16:16:00.000Z

Now that we have 1.2 out the door, we throw ourselves into version 2.0 :) WUA 2.0 will be developed exclusively using Visual Studio.NET 2005 and because there is no "go-live" licence with this yet, we won't be able to put any prototypes or alpha versions on our web site.

For those of you who haven't purchased Flarepath WUA yet, you may not know that we provide access to early versions and beta releases. Unlike other software vendors, we provide access to our daily builds. So, if a customer reports a problem and we fix it, the customer has access to that fix as soon as the build is made.

This is going to be a pretty exciting time for us as we will be learning VS.NET 2005 Team System as well as developing our new version. We are going to compare this new development process against our current system of using Mantis (bug tracking) and Subversion (source code versioning) to see how it stacks up.

Of course, the whole idea for 2.0 is to provide all the great features of Flarepath WUA for WUS. We started defining a feature set a while ago but we are really waiting until Microsoft make WUS RC 1 available for us to finalise things. I'll publish a list of features in a later blog.

Version 1.2

2005-02-26T14:55:00.000Z

We've all been working double time here at Flarpath getting 1.2 ready for release. For the last couple of weeks it's been an endless round of test, fix, test fix....

I hope you enjoy the new features in 1.2 - especially the new SUS Log Reader and I'd really like to hear from anyone who has any feedback on this, Flarepath WUA or the Client Tools.

Glen Conway
Technical Director