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.
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:
- an alias for the DBMS server in question is put in
- some variables are set in accordance with the FreeTDS documentation.
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
You see that I use
lansweeperdb-datasource for the Python ODBC interface, as it is set in the
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 + "%';", 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
# 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.
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