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)

Bulk disabling modules in LibreNMS

So I was in the process of adding in a large quantity of devices into LibreNMS but noticed a few days later the discovery and polling times were getting longer. Not really to be unexpected as there was more devices but some devices took an abnormally amount of time to finish the SNMP scans. Below is an example how to bulk disable attributes under LibreNMS (likely to work on Observium as well).

Outside of globally disabling different modules we will need to talk directly to the MySQL database in order to accomplish what we are looking for.

In the below example I’m looking at disabling the discover_arp-table on all Windows hosts.

select device_id,hostname from devices where OS = 'windows';

Then copy and paste the results into your favorite editor (I generally use excel for data like this).

="insert into devices_attribs (device_id,attrib_type,attrib_value) values ("""&A2&""",""discover_arp-table"",""0"");"

In the above the “A” column contains all the device_id’s I wish to adjust.

This will generate MySQL insert statements like the below

insert into devices_attribs (device_id,attrib_type,attrib_value) values ("4","discover_arp-table","0");

At this point all you need to do is run the generated MySQL statements against the LibreNMS database to disable the given module in bulk.

, ,
October 27, 2014 at 12:06 pm Comments (0)

Filtering out extra Windows interfaces when monitoring with LibreNMS/Observium

By default when monitoring a Windows-based host with LibreNMS (also for Observium), you’ll see a lot of extra unwanted interfaces present. However, to filter them out globally you need to edit the config.php file. Here’s a basic config that should take of most of the extra network adapters present for Windows Server 2008 through 2012R2. Notice, if you have special teaming configurations they will create their own unique names which this will not remove.

$config['bad_if'][] = "wan miniport (sstp)";
$config['bad_if'][] = "wan miniport (l2tp)";
$config['bad_if'][] = "wan miniport (pptp)";
$config['bad_if'][] = "wan miniport (pppoe)";
$config['bad_if'][] = "wan miniport (ipv6)";
$config['bad_if'][] = "wan miniport (network monitor)";
$config['bad_if'][] = "wan miniport (ip)";
$config['bad_if'][] = "wan miniport (ikev2)";
$config['bad_if'][] = "tap-windows adapter v9";
$config['bad_if'][] = "broadcom netlink (tm) gigabit ethernet-wfp lightweight filter-0000";
$config['bad_if'][] = "software loopback interface 1";
$config['bad_if'][] = "lo";
$config['bad_if'][] = "vmxnet3 ethernet adapter-wfp lightweight filter-0000";
$config["bad_if"][] = "vmxnet3 ethernet adapter #2-wfp lightweight filter-0000";
$config['bad_if'][] = "ras async adapter";
$config["bad_if"][] = "teredo tunneling pseudo-interface";
$config["bad_if"][] = "microsoft kernel debug network adapter";
$config["bad_if"][] = "vmxnet3 ethernet adapter-wfp native mac layer lightweight filter-0000";
$config["bad_if"][] = "vmxnet3 ethernet adapter-wfp 802.3 mac layer lightweight filter-0000";
$config["bad_if"][] = "wan miniport(ip)-wfp native mac layer lightweight filter-0000";

I’ve also included a few specifically if you are running on VMware with the VMXNet3 network adapter.

,
October 20, 2014 at 10:05 am Comments (0)

Mass enabling windows features via PowerShell

Recently I needed to add the SNMP service to a few hundred systems. There are few more involved methods for this via unattended installs with SCCM or batch scripted GPO-linked entries but if you want a quick and easy way how, try the below.

Target OS: Server 2008, 2008R2, 2012 and 2012R2

Source system was a 2012R2 box with the AD and ServerManager PowerShell modules installed

Import-Module ActiveDirectory
$myservers = Get-ADComputer -SearchBase "OU=My Servers,DC=liquidobject,DC=com" -Filter "*"
foreach($i in $myservers)
{
 $mysession = New-PSSession -ComputerName $i.name
 Invoke-Command -Session $mysession {Import-Module Servermanager}
 Invoke-Command -Session $mysession {Add-WindowsFeature SNMP-Service}
}

Any server in the OU offline or running an unsupported os (ie Server 2003….we all have them) will throw an error. The above is pretty basic example on how you can install any Windows feature remotely. The remote PSSession and Invoke-Command methods allow you to perform any supported PowerShell command remotely.

In the event your running a legacy version of Windows, there are still options available. The limitation of the below is that it requires you to login to the given system and run the command with an administrative command prompt.

servermanagercmd.exe -install snmp-service
logout
, , , , ,
October 16, 2014 at 1:59 pm Comments (0)

Ubuntu – Clearing up the /boot volume

For years Ubuntu has had the ability to automatically patch the system via crontab, however as kernel updates are applied old revisions are never purged (in the event there is an issue). However, if you have a smaller drive for your install say as a Virtual Machine the /boot volume can be sized pretty small. Eventually you’ll start to see upon login a message warning you the partition is over 80% full, in this case we need to go back and purge some old kernels.

Lets check to see how bad it currently is?

df -h /boot
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       228M   190M  38M  83% /boot

So lets obtain our currently running build information

sudo uname -r

Now, lets take a look at what is currently installed.

sudo dpkg -l linux-image-\* | grep ^ii

Which returned for me returned

ii  linux-image-3.5.0-23-generic           3.5.0-23.35   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-3.5.0-36-generic           3.5.0-36.57   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-3.5.0-37-generic           3.5.0-37.58   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-3.5.0-39-generic           3.5.0-39.60   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-3.5.0-40-generic           3.5.0-40.62   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-3.5.0-44-generic           3.5.0-44.67   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP
ii  linux-image-3.5.0-45-generic           3.5.0-45.68   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP
ii  linux-image-3.5.0-46-generic           3.5.0-46.70   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP
ii  linux-image-3.5.0-47-generic           3.5.0-47.71   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP
ii  linux-image-extra-3.5.0-23-generic     3.5.0-23.35   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-extra-3.5.0-36-generic     3.5.0-36.57   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-extra-3.5.0-37-generic     3.5.0-37.58   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-extra-3.5.0-39-generic     3.5.0-39.60   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-extra-3.5.0-40-generic     3.5.0-40.62   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP  
ii  linux-image-extra-3.5.0-44-generic     3.5.0-44.67   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP
ii  linux-image-extra-3.5.0-45-generic     3.5.0-45.68   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP
ii  linux-image-extra-3.5.0-46-generic     3.5.0-46.70   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP
ii  linux-image-extra-3.5.0-47-generic     3.5.0-47.71   amd64     Linux kernel image for version 3.5.0 on 64 bit x86 SMP
ii  linux-image-generic                    3.5.0.47.63   amd64     Generic Linux kernel image

At this point everything has been stable for a few weeks, so I will purge anything below 3.5.0-47.

sudo apt-get purge linux-image linux-image-3.5.0-23 linux-image-3.5.0-36 linux-image-3.5.0-37 linux-image-3.5.0-39 linux-image-3.5.0-40 linux-image-3.5.0-44 linux-image-3.5.0-45 linux-image-3.5.0-46

After everything was removed, lets check again to see where we sit.

df -h /boot
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       228M   29M  188M  14% /boot

In the end we ended up clearing up 69% of the /boot partition.

,
March 21, 2014 at 8:24 am Comments (0)

vSphere slotsfile 0x10000042 error

Every once and awhile when using SOIC (Storage IO Control) within ESXi the hosts will get out of wack with regards to reading and writing to the slotsfile. If you take a look at your syslog output you might see something like the below.

<14>2013-12-12T06:49:58.987Z vmsrv-13.uwgb.edu storageRM: open /vmfs/volumes//VM-LUN-1/.naa.60002ac0000000000000002900001234/slotsfile(0x10000042, 0x0) failed: No such file or directory
<14>2013-12-12T06:49:58.987Z vmsrv-13.uwgb.edu storageRM: Giving UP No such file or directory Error -1 opening SLOT file /vmfs/volumes//VM-LUN-1/.naa.60002ac0000000000000002900001234/slotsfile
<14>2013-12-12T06:49:58.987Z vmsrv-13.uwgb.edu storageRM: Error -1 in opening & reading the slot file
<14>2013-12-12T06:49:58.987Z vmsrv-13.uwgb.edu storageRM: Failed to read slot file

The correction to this is pretty fortunately is very simple.

Lets stop the SOIC service

/etc/init.d/storageRM stop

Now lets start it

/etc/init.d/storageRM start

Monitor the syslog entries for the next few seconds and you should see them clear up pretty quickly.

Original reference for this post came from Frank over at . Same issue, just seeing in a different area of vSphere.

, ,
December 12, 2013 at 1:03 am Comments (0)

« Older Posts