WSUS synchronization

Introduction

In an enterprise environment, Microsft's"WSUS server" is often used to manage client PCs patches, pushing Windows and Office updates and ensuring that Windows client PCs stay up to date.

Wsus has an MS-SQL server in its core and the tables in this database were examined to see what information was in there that could be useful to FreeNAC users.

The FreeNAC "WSUS module" queries information from the WSUS regularly (e.g. each night) and stores it in dedicated tables in the FreeNAC database.

The script that performs the WSUS synchronization is wsus_sync.php.

Installation

Pre-requisites:

• FreeTDS.
• Wsus must be installed with a full MS-SQL server (not the "light" MSDE), and this module has only been tested with Wsus version 3.0.

Configuration: Wsus

Create an SQL user, which FreeNAC will use to query Wsus. Give this user SELECT rights on the Wsus database.

Enusre that network connectivity to the MS-SQL engine is enabled.

Configuration: FreeNAC

1. The first step is to configure 'FreeTDS' so that SQL queries to Wsus actually work.

Add the following instance to freetds.conf. Adjust the host and perhaps port for your Wsus server:

[wsus_alias]
host = server.domain.com
port = 1433
tds version = 4.2
dump file = /var/log/freetds_inv.log
dump file append = yes
debug level = 1 

2. In the config_en.inc file, set up your username and password in the variables $wsus_dbuser and $wsus_dbpass. These corresponds to the user name and password configured on the MS-SQL server.

3. Set wsus_dbalias and wsus_db in the mysql opennac.config table

update config set value='wsus_dbalias' where name='wsus_dbalias';
update config set value='wsus_db' where name='wsus_db'; 

Here wsus_dbalias is the alias you declared in your freetds.conf file and wsus_db is the database that holds the information regarding the WSUS patches information.

4. Enable the Wsus module in FreeNAC:

update config set value='true' where name='wsus_enabled';

If such a field doesn't exist in your config table, create it as follows:

insert into config set type='boolean', name='wsus_enabled', value='true', comment='Enable or disable the WSUS module';

This flag also enables Wsus related features in the FreeNAC Windows GUI.

Synchronization

Once you have done the above, running the script is very straightforward.

/opt/nac/bin/wsus_sync.php

If you don't receive any errors, that means that all parameters have been properly set.

Operations

Add the following entry in crontab in order to update patch information every day

0   4    * * 1-5 /opt/nac/bin/wsus_sync.php

In this way, it'll run from Monday to Friday at 4:00AM.

Now, in the FreeNAC Windows GUI, Wsus information should be visible in Reports -> Wsus, and for individual end devices in Edit->Wsus.