Use FreeTDS on Linux and Python for Lansweeper Asset Management queries

quick-tip
dbms
python
asset-management
security_management
Tags: #<Tag:0x00007f0ca8f99078> #<Tag:0x00007f0ca8f98f38> #<Tag:0x00007f0ca8f98df8> #<Tag:0x00007f0ca8f98c90> #<Tag:0x00007f0ca8f98ab0>

#1

Everything is possible - even if there is no API

Lansweeper is a network inventory and asset management software, which builds a comprehensive system catalog. It’s automated and easy to setup even in large environments.

The following article is about using Lansweeper’s DB on a Microsoft SQL Server system from Linux and Python to query for asset and user information.

Lansweeper does not offer an API to do so, but you can easily use the DB. It still is a few lines of Python plus two or three config entries.

Doing this can be done by using FreeTDS and ODBC.

  • FreeTDS is the protocol, which is used to interact with Microsoft SQL Server DBMS systems. It’s called Tabular Data Stream, and has got quite some history.
  • ODBC is a programming interface to interact with a DBMS, using a standardized set of functions. In case of MS SQL Server ODBC uses (Free)TDS to connect.

So we need to configure two things: ODBC and FreeTDS; on Linux and Python. This takes 2 minutes. Then we make an SQL query against the Lansweeper DB. That takes 3s. And then we have our user and asset infos available for our security tools.

Worth it!

FreeTDS installation and config

The following example is for the following Ubuntu release:

[email protected]:~$ cat /etc/debian_version
stretch/sid

[email protected]:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.1 LTS
Release:        16.04
Codename:       xenial

And the package version:

dpkg -l | grep freetds
ii  freetds-common                             0.91-6.1build1
                       all          configuration files for FreeTDS SQL client libraries
ii  freetds-dev                                0.91-6.1build1
                       amd64        MS SQL and Sybase client library (static libs and headers)

The paths for the config files differ between Arch and Ubuntu for example. I use Ubuntu as an example, because it’s much more common and because Arch Linux users tend to be the kind of people who just know these things.

[email protected]:~$ cat /etc/freetds.conf
[internal_db]
        host = 10.1.1.X
        port = 1433
        tds version = 7.2
        # client charset = UTF-8

[lansweeper_db]
        host = 10.1.1.X
        port = 1433
        tds version = 7.2
        # client charset = UTF-8

This should appear to be straight forward:

ODBC config

In addition to the DB servers you need to define the data source.

[email protected]:~$ cat /etc/odbc.ini
[internaldb-datasource]
Driver             = FreeTDS
Description         = MSSQL Server
Trace             = No
Server                  = 10.1.1.X
Port                    = 1433
TDS_Version             = 7.2


[lansweeperdb-datasource]
Driver             = FreeTDS
Description         = MSSQL Server
Trace             = No
Server                  = 10.1.1.X
Port                    = 1433
TDS_Version             = 7.2

This looks super similar and it should. Now we tell the ODBC interface to behave and to use FreeTDS as a communication protocol.

[email protected]:~$ cat /etc/odbcinst.ini

[FreeTDS]
Description        = MSSQL Server
Driver            = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
# Setup             = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
CPTimeout         =
CPReuse         =
FileUseage         = 1

Time for Python and Pandas

I use Anaconda as my Python distribution, because conda is a solid Python package manager.

[email protected]:~$ source ~/anaconda2/bin/activate
(root) [email protected]:~$ conda list | grep mssql
pymssql                   2.1.3                    py27_0

(root) [email protected]:~$ conda list | grep pyodb
pyodbc                    3.0.10                   py27_1

(root) [email protected]:~$ conda list | grep pandas
pandas                    0.18.1              np111py27_0

This is for Python 2.7 - that will be relevant later.

Fair enough, so the code:

import pandas
import pyodbc

with open('lansweeper.auth') as f:
    credentials = [ x.strip().split(':') for x in f.readlines()]
for user, password in credentials:
    username = user
    password = password

dsn = 'lansweeperdb-datasource'
database = 'lansweeperdb'

I have the credentials in a file, and not in the code. A smarter approach is to use a standard for config files like ini, yaml or json.

You see that I use lansweeperdb-datasource for the Python ODBC interface, as it is set in the obdc.ini file

Effect is: I have all the preliminaries now to authenticate against the MS SQL server and to access the Lansweeper DB:

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)

Now I keep the code as short as possible and just use Pandas to query for the data:

# Example: computerName is set here to keep it simple. 
# You'd see it in the SIEM logs for example, and
# loop over the computer names
computerName = "COMPANY\Jim-Laptop"

computerName_wo_Domain = computerName.split("\\")
asset_info = pandas.read_sql_query("SELECT * FROM dbo.tblAssets 
WHERE AssetUnique LIKE '%" + computerName_wo_Domain[1] + "%';", cnxn)

So what we do here is: we query the asset management system using the computer name, without the domain prefix. Pandas returns the data into the asset_info object.

 # We normalize the names etc. - this is to make it robust
 user_info[u'Name'] = user_info[u'Name'].map(lambda x: x.encode('ascii', 'ignore').decode('utf-8'))
 user_info[u'Name'] = user_info[u'Username'].map(lambda x: str(x))
 user_info[u'Office'] = user_info[u'Office'].map(lambda x: x.encode('ascii', 'ignore').decode('utf-8'))
 user_info[u'Office'] = user_info[u'Office'].map(lambda x: str(x))
 user_info[u'Description'] = user_info[u'Description'].map(lambda x: x.encode('ascii', 'ignore').decode('utf-8'))
# ...
desc =  "Employee: " + user[u'Lastname'] + ", " +  user[u'Firstname'] + " (" + user[u"Name"] +  ")\n"
desc += "Office: " + user[u"Office"] + "\n"
desc += "Role: " + user[u"Description"] + "\n"

At first this might look impressive: why all the encode and decode magic?

The answer is that for Chinese or Russian names Unicode is required. This code is for Python 2.7, which is bad at handling Unicode with str objects. So we do it this way. It’s a safe Pandas-like way to treat encoding.

Result: given the workstation name, you can see who used it (last logon) and get where she / he works and what her / his role in the company is.

To get this data all you need is a few lines of config plus a few lines of Python. And a read-only account to the Lansweeper DB. That is easy to get.

Summary

Now we can:

  • integrate Lansweeper with security tools and utilize the asset information for log analysis, SIEM etc.
  • use MS SQL servers from Linux (example is Ubuntu)
  • query DBs on MS SQL server systems from Python using Pandas
  • keep the code short, even though we have to do SQL queries
  • use Pandas do deal with all kinds of encoding

This is a quick and easy way to do this, and it’s not a dirty trick :wink: