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