Integrate your SafeConnect NAC with a Palo Alto Firewall

by on Jun.29, 2012, under Code, Tutorials

The following script allows you to export Username:IP Address pairings from your SafeConnect NAC appliance into your Palo Alto firewall.  This allows for super-fast identification of misbehaving clients and infected machines on your network.


  • A Palo Alto firewall and a SafeConnect NAC box (obviously)
  • MySQL database, configured to receive logging from your SafeConnect Appliance (SafeConnect support can help with the log export configuration on your appliance)
  • A Linux box to run this script.   I suggest using the same box as your MySQL DB, but that’s up to you.
  • Two windows servers.  If you’re an AD shop, just install these on any member server.


1) Install the Palo Alto UserID Agent (download from the Palo Alto support site) on two member servers in your domain.  The account you provide to the agent needs permission to read the event logs on the Domain Controllers.  It must also have local administrator access to the box where it is installed.

2) Configure the Palo Alto UserID agent to accept incoming XML connections (Setup -> Edit -> Agent Service)

3) Configure the Access Control List of the PA User-ID Agent program to permit connections from your Linux box and your Palo Alto Firewalls. (Setup -> Access Control List -> Add).  Be sure to permit ports 5006/TCP and 5007/TCP through any applicable firewalls as well.

4) Configure your Palo Alto firewalls to communicate with the UserID Agents.  (From the WebUI, Device -> User Identification -> User-ID Agents).  The port number is 5007.

5) Install MySQL on your Linux box, and configure the SafeConnect appliance for MySQL export to your server.  (The MySQL setup is beyond the scope of this document).  SafeConnect support can assist you with the appliance-side configuration.  Create a MySQL user with permission to read the “clienthist” table from the Linux box where you’ll be running the script.

6) Install the PAN::API Perl Module on your Linux box.  On RHEL, you can drop it into /usr/lib/perl5/site_perl.  The module is available for download from

7) Copy the script to your Linux box:

# Revsion 0.2
# Collects username:IP pairings from your Impulse Point SafeConnect NAC box and and loads the data
# into the Palo Alto Firewall’s UserID agents.  The Palo Alto UserID agent runs on a Windows server;  you’ll
# need two UserID agent boxes to use this script as-written.
# Requires the PAN:API and DBI PERL modules.   You’ll also need to setup MySQL log export from your appliance
# to a MySQL database which is maintained on a separate server.  Ask your SafeConnect support rep for
# assistance in setting up the “BackupDB” export.
# This script was written for, and tested under, Red Hat Linux.
# NOTE:  The PAN::API module does not have proper error handling, and will die if an attempt is made to
# connect to a Palo Alto UserID agent box that is not responding.
# 06.27.12
# Configuration Section #################################

# Your Palo Alto User-ID Agent boxes:


# Your BackupDB MySQL host and user; the specified user needs read access to the “clienthist” table.


# How often do you require users to re-authenticate to SafeConnect, in days?

# Maximum number of submissions to the PA UID Agent per session (100 seems to work well).

# Enable debugging (yes/no).  Generates a lot of output, use with caution.


#### End of Configuration Section ####

use DBI();
use PAN::API;

# Create PAN::API Objects


# Connection to your SafeConnect BackupDB instance

my $dbobject = DBI->connect(“DBI:mysql:database=backupDB;host=$mysql_server”,
$mysql_username, $mysql_password, {‘RaiseError’ => 1});

# MySQL query string.  Pulls the last $safeconnect_reauth_time days of data.

SELECT transDate,currentIpAddress,principal from clienthist where
DATE_SUB(CURDATE(), INTERVAL $safeconnect_reauth_time DAY) <= transDate order by transDate asc;

my $queryobject = $dbobject->prepare($query);


while (@row = $queryobject->fetchrow_array()) {

# Only process those entries with a username present..

if ( $row[2] ) {
($username, $groups)=split(“,”, $row[2]);
if ( $debug eq “yes” ) {
print “Found pairing:  $row[0] $row[1] –> $username \n”;

# Close the connection to the BackupDB



# Process collected data

foreach $ip ( keys %ipdb ) {

if ( $ipdb{$ip} eq “null” ) {

# ignore “null” entries – indicates user has policy key installed but has
# not logged in through the web interface

} else {

if ( $debug eq “yes” ) {
print “Processing $ip –> $ipdb{$ip}\n”;

# Create the XML entries for this IP:Username pair

if ( $count eq $XMLSize ) {
# Submit data to the agent in batches of $XMLSize

if ( $debug eq “yes” ) {
print “>> Submitting batch to $server1\n”;


if ( $debug eq “yes” ) {
print “>> Submitting batch to $server2\n”;



# Submit any remaining entries

if ( $debug eq “yes” ) {
print “>> Submitting final batch to $server1\n”;


if ( $debug eq “yes” ) {
print “>> Submitting final batch to $server2\n”;


# Done

8) Run the script.  If everything is configured properly, you’ll see username:IP pairings being retrieved from your database and transmitted to the Palo Alto UserID agent boxes:

Found pairing:  2012-06-29 09:18:24 –> jdoe01
Found pairing:  2012-06-29 09:18:26 –> jdoe01
Found pairing:  2012-06-29 09:19:18 –> jdoe02
Found pairing:  2012-06-29 09:19:19 –> jdoe02
Found pairing:  2012-06-29 09:19:24 –> jdoe07
Found pairing:  2012-06-29 09:20:09 –> jdoe02
Found pairing:  2012-06-29 09:20:10 –> jdoe07
Found pairing:  2012-06-29 09:20:19 –> jdoe31
>> Submitting batch to
>> Submitting batch to

9) Check the Palo Alto UserID agent’s GUI.  Under the “Monitoring” tab, you’ll see the new entries appear.

10) Configure your Linux box to run the script periodically.  Once every four hours seems about right for an environment where users must re-authenticate once every seven days.  Adjust accordingly.

11) WIN!  Your Palo Alto firewall will now tag any applicable log entries with the corresponding username.

I hope this has been helpful.  Please leave any questions or comments in the forum below.


2 Comments for this entry

  • Apachez

    Its a matter of taste but I prefer using placeholders for added security in order to avoid sql injections (no matter if its for personal use or public use 🙂

    Like so:

    my %sqlconfig = (
    ‘server’ => ‘’,
    ‘user’ => ‘MySQL_username’,
    ‘pass’ => ‘MySQL_password’,
    ‘db’ => ‘backupDB’,

    my %sql = ();

    my $dbh = DBI->connect(“DBI:mysql:”.$sqlconfig->{‘db’}.”;”.$sqlconfig->{‘server’}, $sqlconfig->{‘user’}, $sqlconfig->{‘pass’}) || Error(“Couldn’t connect to database.”);

    my $sth = $dbh->prepare_cached(“SELECT transDate, currentIpAddress, principal FROM clienthist WHERE DATE_SUB(CURDATE(), INTERVAL ? DAY) execute($safeconnect_reauth_time) || Error(“Couldn’t find requested items.”);

    $sth->bind_columns(undef, \$sql{‘transDate’}, \$sql{‘currentIpAddress’}, \$sql{‘principal’});

    while($sth->fetchrow_arrayref) {
    …whatever you wish to do, you access each column like: $sql{‘transDate’}, $sql{‘currentIpAddress’} and $sql{‘principal’}…



  • Heather

    Great work on the article, SQL is probably my weakest point in coding and this helps a lot.

Leave a Reply

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!