LiquidObject

Microsoft Network Access Protection Reporting

One of the weak components of Microsoft’s NAP implementation is at it’s core the base functionality is there but reporting capabilities were lacking. Even more so in the event you had to deal with multiple NAP servers running. Beginning with the 2008R2 implementation and going forward there is the ability to optionally use SQL server as the back-end for logging instead of flat text files. However, that’s as far as Microsoft went with it.

With running multiple NAP servers you can aggregate the logging into a single SQL database which gives you the ability to some of your own custom reporting as the data is there but just needs to be queried. Below are some sample PowerShell scripts for getting at least some minimal reporting out of your installs.

Find-User.ps1 locate user based upon a partial username match.

$SqlServer = "SQL-1.liquidobject.com"
$SqlCatalog = "NPS"

function SQLSelect
{
    param($SqlQuery)
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    return $DataSet.Tables[0]
}


if($args[0] -ne $NULL)
{
    $myuser = $args[0]
    SQLSelect("select id,timestamp,computer_name as Server,packet_type,User_name,Client_IP_Address as Server_IP,NP_Policy_Name,Called_Station_ID,Quarantine_Update_Non_Compliant as Compliant from accounting_data where user_name like '$myuser%' order by id desc") | FT -AutoSize
}
else
{
    Write-host The script needs to be called in the format: .\Find-User.ps1 username
    $error = 1
}

Get-GeneralStatistics.ps1 shows per NPS server how the load has been balanced across a pair of NPS servers and total usage by authentication method.

$SqlServer = "SQL-1.liquidobject.com"
$SqlCatalog = "NPS"


function SQLSelect
{
    param($SqlQuery)
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    return $DataSet.Tables[0]
}
Clear

$NPS1_Count = (SQLSelect("select count(*) from dbo.accounting_data where computer_name = 'NPS-1'")).column1
$NPS2_Count = (SQLSelect("select count(*) from dbo.accounting_data where computer_name = 'NPS-2'")).column1
if($NPS1_Count -gt $NPS2_Count)
{
    $NPS1_Load = [Math]::Round((1-($NPS2_Count / $NPS1_Count))*100,2)
    $NPS2_Load = [Math]::Round(100-$NPS1_Load,2)
}
else
{
    $NPS2_Load = [Math]::Round((1-($NPS1_Count / $NPS2_Count))*100,2)
    $NPS1_Load = [Math]::Round(100-$NPS2_Load,2)

}
Write-Host "`n`n                    Radius Statistics`n"
Write-Host(" ------------------------------------------------------------------------------`n")
Write-Host " NPS-1 has $NPS1_Count status updates`n NPS-1 has $NPS1_Load% of the historical load.`n`n"
Write-Host " NPS-2 has $NPS2_Count status updates`n NPS-2 has $NPS2_Load% of the historical load.`n`n"


$Secure_Count = (SQLSelect("select count(*) from accounting_data where NP_Policy_Name = 'Secure Wireless Connections'")).column1
$EDURoam_Count = (SQLSelect("select count(*) from accounting_data where NP_Policy_Name = 'eduroam'")).column1

Write-Host(" ------------------------------------------------------------------------------`n")
Write-Host(" We have had $Secure_Count Secure Wireless authentication status updates.`n")
Write-Host(" We have had $EDURoam_Count eduroam authentication status updates.`n`n")

Write-Host(" ------------------------------------------------------------------------------`n")

Get-Last50Rows.ps1 returns the last 50 entries in the database.

$SqlServer = "SQL-1.liquidobject.com"
$SqlCatalog = "NPS"

function SQLSelect
{
    param($SqlQuery)
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    return $DataSet.Tables[0]
}
Clear


SQLSelect("select TOP 50 id,timestamp,computer_name as Server,packet_type,User_name,Client_IP_Address as Aruba_IP,NP_Policy_Name,Called_Station_ID,Quarantine_Update_Non_Compliant as Compliant from accounting_data order by id desc") | FT -AutoSize

Get-Table.ps1 in the event you need to do some diagnostics, this shows the raw output from the last 1,000 rows.

$SqlServer = "SQL-1.liquidobject.com"
$SqlCatalog = "NPS"

function SQLSelect
{
    param($SqlQuery)
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    return $DataSet.Tables[0]
}
Clear


SQLSelect("select Top 1000 * from accounting_data order by id desc") | FT -AutoSize

Pause
, , ,
February 18, 2015 at 8:20 am Comments (0)

WSUS repair after backup restoration

Last week during the process of updating a few services one of the WSUS servers had gone belly up. Installing over the top or performing an uninstall then a re-install did not clear up the issue. The next step was to perform a restore on the system (dedicated WSUS/Forefront for Exchange VM) from the last backup taken before the system started going south. The restore worked bringing back a WSUS instance which would talk to the clients without any issues but then I started seeing errors in the application event log. The errors were showing that recently approved updates which were in the database could not be found on the filesystem.

What I ran into was a catch-22 when performing an enterprise deployment of WSUS where the SQL instance is on another VM separate from the WSUS server. Restoring the SQL database is possible but it may or may not be exactly when the backup of the WSUS instance was so I opted the other option which allows for the existing database to remain intact.

1) Stop the “Update Services” and two IIS services
2) Delete everything found in the WsusContent folder, while it can be skipped I wanted to make sure everything was cleanly downloaded
3) Open up a command line
4) cd “\Program Files\Update Services\Tools
5) wsusutil.exe reset

This will issue a full verification of the WSUS installation and pull down all of the approved updates. Which at this point the 30GB or so of updates took a few hours to rebuild the entire local cache. At which time, updates began to flow normally.

, , , , ,
August 24, 2012 at 9:42 am Comments (0)

WSUS Syncronization logging

When working with a WSUS console if you ever need to look at the Syncronizations it takes a long time to load. This is due to the way the logging functionality was designed which over months or years can cause this pain to minutes to load in some environment. However, this data can be cleaned to provide much faster load times.

If you want to purge the entire Syncronization history:

DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389');

Otherwise if you want to keep the more recent records you could do something like the following:

DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389') AND DATEDIFF(month, TimeAtServer, CURRENT_TIMESTAMP) >= 2;
,
June 4, 2012 at 11:21 am Comments (0)

Windows Internal Database

During the install WSUS under Server 2003 or as an optional component to be installed under Server 2008/R2 there is an option to use the “Windows Internal Database”. This database is based upon a stripped down version of SQL Server 2005 Express with three exceptions. First there is no database size limitations, any management of the database needs to be done locally either via command line or SQL Management Studio, and lastly there are no licensing restrictions (Moving to SQL Server Standard or higher requires SQL Client CALs or per-socket licensing in which both cost significantly more).

By default upon installation any databases will be installed under “C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data” as you may wish to adjust your antivirus scanning for this particular folder.

In the event you wish to manage the internal database via SQL Management Studio login to the server with administrative privileges with a server name of \\.\pipe\mssql$microsoft##ssee\sql\query while using Windows Authentication.

, , , , ,
December 1, 2011 at 2:18 pm Comments (0)