list hosts with a specific package installed

[lanmand]

is it possible to list hosts with a specific package installed either via cmd-line or api ?

[gdelafond]

Hello

You can do a SQL request to get it :

package="linux-image-amd64%"
mysql -e "select hosts_host.hostname as Host,operatingsystems_os.name as OS,DATE_FORMAT(lastreport, '%Y/%m/%d %H:%i') as Report,packages_packagename.name as Package from hosts_host INNER JOIN hosts_host_packages ON hosts_host.id = hosts_host_packages.host_id INNER JOIN operatingsystems_os ON hosts_host.os_id = operatingsystems_os.id INNER JOIN packages_package ON hosts_host_packages.package_id=packages_package.id INNER JOIN packages_packagename ON packages_package.name_id = packages_packagename.id WHERE packages_packagename.name LIKE '${package}' ORDER BY hosts_host.hostname" patchman

[lanmand]

Hi gdelafond, Great tip, i created a python script to run the query, if it can help others.

import csv
import os
import sqlite3

# run like this:
# python pm-pkg-list.py

##############
# FILE DEFN. #
##############
filePath = '/root/'
fileName = 'pm-pkg-list-export.csv'

############
# Database #
############
database = '/var/lib/patchman/db/patchman.db'
connect = None

# Check if database file exists.
if not os.path.isfile(database):

    # Confirm incorrect database location and stop program execution.
    print("Error locating database.")
    quit()

# Check if the file path exists.
if os.path.exists(filePath):

    try:

        # Connect to database.
        connect = sqlite3.connect(database)

    except sqlite3.DatabaseError as e:

        # Confirm unsuccessful connection and quit.
        print("Database connection unsuccessful.")
        quit()

    # Cursor to execute query.
    cursor = connect.cursor()

    # SQL to select data from the person table.
    sqlSelect = \
        "SELECT hosts_host.hostname as Host,operatingsystems_os.name as OS,packages_packagename.name as Package,packages_package.version as Version,packages_package.release as Release \
         FROM hosts_host INNER JOIN hosts_host_packages ON hosts_host.id = hosts_host_packages.host_id INNER JOIN operatingsystems_os ON hosts_host.os_id = operatingsystems_os.id INNER JOIN packages_package ON hosts_host_packages.package_id=packages_package.id INNER JOIN packages_packagename ON packages_package.name_id = packages_packagename.id \
         WHERE packages_packagename.name LIKE 'openssh-server' \
         ORDER BY hosts_host.hostname"

    try:

        # Execute query.
        cursor.execute(sqlSelect)

        # Fetch the data returned.
        results = cursor.fetchall()

        # Extract the table headers.
        headers = [i[0] for i in cursor.description]

        # Open CSV file for writing.
        csvFile = csv.writer(open(filePath + fileName, 'w'),
                             delimiter=',', lineterminator='\r\n',
                             quoting=csv.QUOTE_ALL, escapechar='\\')

        # Add the headers and data to the CSV file.
        csvFile.writerow(headers)
        csvFile.writerows(results)

        # Message stating export successful.
        print("Data export successful.")

    except sqlite3.DatabaseError as e:

        # Message stating export unsuccessful.
        print("Data export unsuccessful.")
        quit()

    finally:

        # Close database connection.
        connect.close()

else:

    # Message stating file path does not exist.
    print("File path does not exist.")

[furlongm]

There is also a REST API that you could use to get the list