Table: jumpcloud_device_linux_package - Query JumpCloud Linux Packages using SQL

JumpCloud is a cloud-based directory service that allows IT admins to control user identities and resource access. The Linux Packages in JumpCloud provide details about the software packages installed on a Linux device. It helps in maintaining an updated software inventory and identifying potential software vulnerabilities.

Table Usage Guide

The jumpcloud_device_linux_package table provides insights into Linux packages installed on devices managed by JumpCloud. As a system administrator, explore package-specific details through this table, including package names, versions, and installation status. Utilize it to maintain an updated software inventory, identify outdated packages, and uncover potential vulnerabilities due to unpatched or deprecated software.

Important Notes

To query all applications installed in a MacOS or a Windows device, use the jumpcloud_device_macos_app and jumpcloud_device_windows_program tables respectively.

Examples

Basic info

Explore which Linux packages have been installed on your Jumpcloud devices, along with their versions and installation times. This can help in managing device software and identifying any outdated or unnecessary packages.

select
  name,
  version,
  install_time,
  size,
  device_id
from
  jumpcloud_device_linux_package;

select
  name,
  version,
  install_time,
  size,
  device_id
from
  jumpcloud_device_linux_package;

Get the device information

Explore the installed software packages on your devices. This allows you to understand what applications are installed on each device, their versions, and when they were installed, which can be crucial for managing software updates and ensuring device security.

select
  d.display_name as device_name,
  d.serial_number,
  a.name as package_name,
  a.version as package_version,
  a.install_time
from
  jumpcloud_device_linux_package as a
  join jumpcloud_device as d on d.id = a.device_id;

select
  d.display_name as device_name,
  d.serial_number,
  a.name as package_name,
  a.version as package_version,
  a.install_time
from
  jumpcloud_device_linux_package as a
  join jumpcloud_device as d on d.id = a.device_id;

List devices with tailscale app installed

Discover the devices that have the Tailscale app installed. This can be useful to assess the spread and usage of the app within your network.

select
  d.display_name as device_name,
  d.serial_number,
  a.name as package_name,
  a.version as package_version,
  a.install_time
from
  jumpcloud_device_linux_package as a
  join jumpcloud_device as d on d.id = a.device_id
where
  a.name ilike 'tailscale%';

select
  d.display_name as device_name,
  d.serial_number,
  a.name as package_name,
  a.version as package_version,
  a.install_time
from
  jumpcloud_device_linux_package as a
  join jumpcloud_device as d on d.id = a.device_id
where
  a.name like 'tailscale%';

List computers with an older version of zoom app (< 5.12)

Determine the areas in which devices are running an outdated version of the Zoom application. This can help in identifying devices that need to be updated for better security and improved features.

select
  d.display_name as device_name,
  d.serial_number,
  a.name as package_name,
  a.version as package_version,
  a.install_time
from
  jumpcloud_device_linux_package as a
  join jumpcloud_device as d on d.id = a.device_id
where
  a.name ilike 'zoom%'
  and string_to_array(split_part(a.version, ' ', 1), '.') :: int [ ] < string_to_array('5.12', '.') :: int [ ];

Error: SQLite does not support string_to_array
and split functions.

List all packages installed in last 24 hours

Explore the recent system updates by identifying all software packages installed within the last day. This can help in tracking system changes and troubleshooting any issues that may arise due to the new installations.

select
  name,
  version,
  install_time,
  device_id
from
  jumpcloud_device_linux_package
where
  install_time >= (current_timestamp - interval '1 day')
order by
  install_time desc;

select
  name,
  version,
  install_time,
  device_id
from
  jumpcloud_device_linux_package
where
  install_time >= datetime('now', '-1 day')
order by
  install_time desc;

Schema for jumpcloud_device_linux_package

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
arch	text		Specifies the package architecture.
collection_time	timestamp with time zone		The time when the data was collected by the JumpCloud agent.
device_id	text	=	A JumpCloud generated unique identifier for the device.
install_time	timestamp with time zone		The time when the package was installed.
maintainer_or_vendor	text		The name of the maintainer or vendor of the package.
mount_namespace_id	text		The mount name space ID of the package.
name	text		The name of the package.
organization_id	text	=, !=, ~~, ~~, !~~, !~~	Specifies the ID of the organization.
package_format	text		The format of the package.
package_group_or_section	text		Specifies the package group or section.
pid_with_namespace	bigint		Specifies the PID with namespace.
release_or_revision	text		Specifies the release or revision of the package.
size	text		Specifies the size of the package.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
title	text		Title of the resource.
version	text		The installed version of the package.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- jumpcloud

You can pass the configuration to the command with the --config argument:

steampipe_export_jumpcloud --config '<your_config>' jumpcloud_device_linux_package