Tag Archives: jss

Incorporate Sal and JSS Data Into WebHelpDesk Inventory, using Docker

In previous posts, I covered:

WebHelpDesk, among its other features, makes a great inventory aggregate collector thanks to its use of discovery connections. Inventory data can be easily pulled from any flat database. Sal is a reporting engine for Munki that collects inventory data about OS X Munki clients, and JAMF Casper as an iOS MDM (referred to as Casper or “JSS” from here on out) stores inventory data about iOS clients.

We can set up scripts to pull data from Sal, using Sal-WHDImport, and from Casper, using JSSImport. This makes for a great triangle, allowing inventory aggregation into WebHelpDesk, and this is relatively trivial with Docker.

To save some time, I’ve incorporated the Sal-WHDImport script into Sal itself in a Dockerfile, available as the Sal-WHD container. We’ll be using this container below.

I’ve done the same thing with the JSSImport script, creating the JSSImport container.

Preparing Data Files:

Sal requires some modification in order to talk to WebHelpDesk. We’re going to use a plugin Graham Gilbert wrote called WHDImport to sync the Sal data into a single flat database for WebHelpDesk to pull from.

First, we’ll need to modify settings.py. On the Docker host:

  1. mkdir -p /usr/local/sal_data/settings/
  2. curl -o /usr/local/sal_data/settings/settings.py https://raw.githubusercontent.com/macadmins/sal/master/settings.py

Make the following changes to settings.py:
Add 'whdimport', (with the comma) to the end of the list of INSTALLED_APPS.

Next, we’ll clone a copy of MacModelShelf:
git clone https://github.com/nmcspadden/MacModelShelf.git /usr/local/sal_data/macmodelshelf

MacModelShelf was originally developed by Per Oloffson, but this version is my fork that uses a JSON database, which seems to improve cross-platform compatibility. The purpose of cloning a local copy is to keep the JSON database, which is automatically populated with model lookups. By keeping a local copy, we can safely spin up and down WebHelpDesk containers without losing any of our lookup data (which may save milliseconds in future lookups).

Run the Sal DB and Setup Scripts:

First, we create a data-only container for Sal’s Postgres database, and then run the Postgres database. We can specify all the variables at runtime using the -e arguments. The only thing you’ll need to change below is the password.

  1. docker run --name "sal-db-data" -d --entrypoint /bin/echo grahamgilbert/postgres Data-only container for postgres-sal
  2. docker run --name "postgres-sal" -d --volumes-from sal-db-data -e DB_NAME=sal -e DB_USER=saldbadmin -e DB_PASS=password --restart="always" grahamgilbert/postgres

Run the JSS Import DB:

We do the same thing with the JSS Import container’s database. Again, change the password only. Note that we’re using a slightly different Postgres container for this – the macadmins/postgres instead of grahamgilbert/postgres.

  1. docker run --name "jssi-db-data" -d --entrypoint /bin/echo macadmins/postgres Data-only container for jssimport-db
  2. docker run --name "jssimport-db" -d --volumes-from jssi-db-data -e DB_NAME=jssimport -e DB_USER=jssdbadmin -e DB_PASS=password --restart="always" macadmins/postgres

Run the WHD DB:

There’s a theme here – change the password for WebHelpDesk’s Postgres database.

  1. docker run -d --name whd-db-data --entrypoint /bin/echo macadmins/postgres Data-only container for postgres-whd
  2. docker run -d --name postgres-whd --volumes-from whd-db-data -e DB_NAME=whd -e DB_USER=whddbadmin -e DB_PASS=password --restart="always" macadmins/postgres

Run Temporary Sal to Prepare Initial Data Migration:

Load a temporary container just for the purpose of setting up Sal’s Django backend to incorporate the WHDImport addition.

Note that we’re using --rm with this docker run command, because this is intended only to be a transient container for the purpose of setting up the database. It will remove itself when complete, but the changes to the database will be permanent.

docker run --name "sal-loaddata" --link postgres-sal:db -e ADMIN_PASS=password -e DB_NAME=sal -e DB_USER=saldbadmin -e DB_PASS=password -it --rm -v /usr/local/sal_data/settings/settings.py:/home/docker/sal/sal/settings.py macadmins/salwhd /bin/bash

This opens a Bash shell. From that Bash shell:

  1. cd /home/docker/sal
  2. python manage.py syncdb --noinput
  3. python manage.py migrate --noinput
  4. echo "TRUNCATE django_content_type CASCADE;" | python manage.py dbshell | xargs
  5. python manage.py schemamigration whdimport --auto
  6. python manage.py migrate whdimport
  7. exit
  8. After exiting, the temporary “sal-loaddata” container is removed.

Run Sal and Sync the Database:

Load up the Sal container and run “syncmachines” to get started. Change the passwords here to match what you used previously:

  1. docker run -d --name sal -p 80:8000 --link postgres-sal:db -e ADMIN_PASS=password -e DB_NAME=sal -e DB_USER=saldbadmin -e DB_PASS=password -v /usr/local/sal_data/settings/settings.py:/home/docker/sal/sal/settings.py --restart="always" macadmins/salwhd
  2. docker exec sal python /home/docker/sal/manage.py syncmachines

Run JSSImport and Sync the Database:

Run the JSSImport container, which will pull the device list from Casper and sync it into the jssimport database.

If you haven’t already, set up an API-only user account in the JSS, and use those credentials below. Change the URL to match your Casper instance.

docker run --rm --name jssi --link jssimport-db:db -e DB_NAME=jssimport -e DB_USER=jssdbadmin -e DB_PASS=password -e JSS_USER=user -e JSS_PASS=password -e JSS_URL=https://casper.domain.com:8443 --restart="always" macadmins/jssimport

Although I haven’t tested this particular permutation, you could theoretically build a JSS Docker instance, and then link it to the jssimport container (--link jss:jss), and just use the URL -e JSS_URL=https://casper.

Run WHD with its data-only container:

Now run WebHelpDesk with its linked databases.

  1. docker run -d --name whd-data --entrypoint /bin/echo macadmins/whd Data-only container for whd
  2. docker run -d -p 8081:8081 --link postgres-sal:saldb --link postgres-whd:db --link jssimport-db:jdb --name "whd" --volumes-from whd-data --restart="always" macadmins/whd

WebHelpDesk now has direct access to three linked databases – its own Postgres database, as db; the Sal database, known as saldb; and the JSS Import database, known as jdb. This will make it trivially easy to pull the data it needs.

Configure WHD Through Browser:

  1. Open your web browser on the Docker host: http://localhost:8081
  2. Set up using Custom SQL Database:
    1. Database type: postgreSQL (External)
    2. Host: db
    3. Port: 5432
    4. Database Name: whd
    5. Username: whddbadmin
    6. Password: password
  3. Skip email customization
  4. Setup administrative account/password
  5. Skip the ticket customization

Setup Discovery Connections:

In WebHelpDesk, go to Setup > Assets > Discovery Connections. Make your two connections for Sal and the JSS.

  1. Setup discovery disconnection “Sal”:
    1. Connection Name: “Sal” (whatever you want)
    2. Discovery Tool: Database Table or View
    3. Database Type: PostgreSQL – uncheck Use Embedded Database
    4. Host: saldb
    5. Port: 5432
    6. Database Name: sal
    7. Username: saldbadmin
    8. Password: password
    9. Schema: Public
    10. Table or View: whdimport_whdmachine
    11. Sync Column: serial
  2. Setup discovery connection “Casper”:
    1. Connection Name: “Casper” (whatever you want)
    2. Discovery Tool: Database Table or View
    3. Database Type: PostgreSQL – uncheck Use Embedded Database
    4. Host: jdb
    5. Port: 5432
    6. Database Name: jssimport
    7. Username: jssdbadmin
    8. Password: password
    9. Schema: Public
    10. Table or View: casperimport
    11. Sync Column: serial

Now, you have a single web service that handles all inventory collection.

From here, if you wanted to schedule this for automation, you’d only need to run these two tasks regularly:

  1. docker exec sal python /home/docker/sal/manage.py syncmachines (since the sal container is daemonized and runs persistently).
  2. docker run --rm --name jssi --link jssimport-db:db -e DB_NAME=jssimport -e DB_USER=jssdbadmin -e DB_PASS=password -e JSS_USER=user -e JSS_PASS=password -e JSS_URL=https://casper.domain.com:8443 macadmins/jssimport (since this container is a fire-and-forget container that self-deletes on completion).

You could set up a crontab to run those two tasks nightly, and then set up WebHelpDesk’s internal syncs to its discovery connections to occur just an hour or so afterwards.

Once your inventory data is aggregated, you could use other tools like my WHD-CLI script to access WebHelpDesk via a Python interpreter, allowing for more scriptability. This is also available in a Docker container.

Using WHD-CLI, you have instant scriptable access to your inventory system, which could be used for lots of neat things, including a way to guarantee that a Puppetmaster only signs approved devices. Lots to explore!

Importing JAMF Casper Suite Data into WebHelpDesk Inventory with Docker

One of [WebHelpDesk]’s many useful features is the ability to set up discovery connections that pull data from other sources into its own database.

WebHelpDesk comes with a built in JAMF Casper Suite connection (which will be referred to as “JSS” from here on), but it only applies to Computers. If you have Casper for iOS, it doesn’t pull iOS devices. I have Casper only for iOS devices, with no Computers at all, so this discovery connection fails for me.

That’s not a big problem, though, because you can also set up a raw database or table view to pull data from instead. Since WHD can pull data from any database it can access, we just need to find a way to get that JSS data into a nice flat database.

The JSS does have its own MySQL database to store data in, but in general we don’t really want to access that directly. We want to keep our databases separate and isolated from each other, both for safety and robustness. Also, the tables and ways that Casper stores information into its database are private implementation details, and we can’t rely on those being the same between updates. It’s entirely possible that Casper 9.7, or Casper 10, will change existing database structure, and any discovery connection we set up in WebHelpDesk that pulls data from certain tables might break. Not much point setting up an automation that can’t be relied upon.

Instead, Docker makes it easy for us to set up a quick database for WHD to access. All that needs to be done is writing a script that pulls data from Casper and populates the database, so that WHD can use a discovery connection to access it.

Writing the script:

The repo for this script can be found on Github here.

The script makes use of Shea Craig’s incredible Python-JSS tool, which provides a Python command line interface to the JSS’s REST API, also documented on MacBrained.org.

The script requires the presence of two files:

The database preferences file needs to have credentials for access to a Postgres database to store the data in for WHD to pull from.

The JSS preferences file needs to have credentials for access to a Casper instance, along with a username and password with API permissions.

Rather than repost the entire script, I’m going to discuss two specific functions:

CreateCasperImportTable(conn)
This function takes an SQL connection (created by the psycopg2 connector) and then creates the basic Casper table that we’re going to populate, if it doesn’t already exist (thus providing idempotence).

SubmitSQLForDevice(thisDevice, conn, j)
This is the main meat of the script – it’s also known as an “upsert” from the Postgres manual. It takes a given device (i.e. a single device record polled from the JSS), creates a new function called merge_db that contains all the fields from that device that are relevant, and then updates the record in the new database table if it exists, otherwise creates it if not already found.

Since we have a working script that pulls data from the JSS via API and puts it into a Postgres container, it seems only natural to Dockerize it.

Creating a Docker image:

The Docker image can be pulled from the Docker hub here.

The Dockerfile:

FROM debian

MAINTAINER Nick McSpadden <nmcspadden@gmail.com>

ENV APP_DIR /home/jssi
ENV DB_HOST db
ENV DB_NAME jssimport
ENV DB_USER jssdbadmin
ENV DB_PASS password

ENV JSS_USER user
ENV JSS_PASS password
ENV JSS_URL https://casper:8443/

RUN apt-get update && apt-get install -y python-setuptools python-psycopg2 && apt-get clean
RUN rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*

ADD https://github.com/sheagcraig/python-jss/tarball/master /usr/local/python-jss/master.tar.gz
RUN tar -zxvf /usr/local/python-jss/master.tar.gz --strip-components=1 -C /usr/local/python-jss && rm /usr/local/python-jss/master.tar.gz
WORKDIR /usr/local/python-jss
RUN python /usr/local/python-jss/setup.py install

ADD https://github.com/nmcspadden/JSSImport/tarball/master $APP_DIR/master.tar.gz
RUN tar -zxvf /home/jssi/master.tar.gz --strip-components=1 -C /home/jssi/ && rm /home/jssi/master.tar.gz

ADD run.sh /run.sh
RUN chmod 755 /run.sh

CMD ["/run.sh"]

Aside from the epic amount of environmental variables, the Dockerfile is fairly simple. Install Python’s setuptools and pyscopg2 module, and then install python-jss by running setup.py install. Add the JSSImport script.

Finally, add the runtime execution script, which replaces the contents of the two preference files (the JSON and Plist files) with the environmental variables, and then executes the sync.

This container is not intended to be a daemonized running container. It executes the JSSImport script and then stops, so using –rm for each execution is ideal – we don’t really need it to linger around after finishing since it doesn’t do anything on its own.

Running the Docker image:

Start a data-only container for the Postgres database:
docker run --name "jssi-db-data" -d --entrypoint /bin/echo macadmins/postgres Data-only container for jssimport-db
Start the database, change variables as necessary:
docker run --name "jssimport-db" -d --volumes-from jssi-db-data -e DB_NAME=jssimport -e DB_USER=jssdbadmin -e DB_PASS=password --restart="always" macadmins/postgres

Run the container, which will execute the JSSPull script and then delete itself:
docker run --rm --name jssi --link jssimport-db:db -e DB_NAME=jssimport -e DB_USER=jssdbadmin -e DB_PASS=password -e JSS_USER=user -e JSS_PASS=password -e JSS_URL=https://casper.domain.com:8443 macadmins/jssimport
[Note: This assumes you have a running Casper install, or have a JSS container you can link. That’s outside the scope of this post.]

The jssimport-db database container is now populated with the mobile device list from the provided JSS, and can be sourced for WebHelpDesk’s discovery connections.

Configuring WebHelpDesk to use it:

You’ll want to run a WebHelpDesk docker container. For more information about that, see my previous blog post on running WebHelpDesk in Docker.

However, since we’re now adding in a new database, you’ll want to run the WebHelpDesk container with our additional jssimport-db database linked in:
docker run -d -p 8081:8081 --link postgres-whd:db --link jssimport-db:jdb --name "whd" macadmins/whd

Once you’ve done the basic configuration of WebHelpDesk (also covered in the previous post above), you can now configure a discovery connection for our JSSImport database:

  1. Connection Name: “Casper” (whatever you want)
  2. Discovery Tool: Database Table or View
    1. Database Type: PostgreSQL – uncheck Use Embedded Database
    2. Host: jdb
    3. Port: 5432
    4. Database Name: jssimport
    5. Username: jssdbadmin
    6. Password: password
    7. Schema: Public
    8. Table or View: casperimport
    9. Sync Column: serial

Next you’ll need to map the fields in the “Attribute Mapping” section so that the appropriate database columns get mapped to the appropriate WHD fields. (Example: “serial” maps to WHD field “Serial No.”, “macaddress” to WHD field “MAC address, etc.”)

Once attributes are mapped, save the connection, go back and hit “Sync Now” and watch the import.

The end result should be that WebHelpDesk now pulls in all the mobile devices from your Casper instance, through the roundabout means of API -> database -> discovery connection.