Table: ansible_playbook - Query Ansible Playbooks using SQL

Ansible Playbook is a set of instructions that Ansible will execute on the target host or hosts. It is the primary mechanism for system configuration management in Ansible and is written in YAML. Playbooks can declare configurations, orchestrate steps of any manual ordered process, and even interact with other tools and services.

Table Usage Guide

The ansible_playbook table provides insights into playbooks within Ansible. As a DevOps engineer, explore playbook-specific details through this table, including the tasks, handlers, and associated metadata. Utilize it to uncover information about playbooks, such as those with errors, the sequence of tasks, and the verification of handlers.

Examples

Retrieve all playbooks

Explore which playbooks are available in your Ansible configuration. This allows you to gain insights into the tasks, variables, and hosts associated with each playbook, and understand their respective paths.

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook;

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook;

List playbooks targeting specific hosts

Explore which ansible playbooks are specifically targeting your web servers. This can help you manage and optimize the deployment of updates or changes across your server infrastructure.

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook
where
  hosts = 'web_servers';

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook
where
  hosts = 'web_servers';

List playbooks that use privilege escalation

Explore which Ansible playbooks are using privilege escalation. This can be helpful to assess security practices and identify potential areas of risk in your infrastructure setup.

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook
where
  become;

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook
where
  become = 1;

List playbooks with no handlers

Explore which Ansible playbooks lack handlers, providing a way to identify potential areas for adding error or event handling to improve playbook robustness and reliability.

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook
where
  handlers is null;

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook
where
  handlers is null;

List playbooks that use `root` privilege

Explore which playbooks are utilizing root privileges. This can be beneficial to identify potential security risks and ensure best practices are adhered to.

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook
where
  become
  and (
    become_user is null
    or become_user = 'root'
  );

select
  name,
  hosts,
  tasks,
  vars,
  path
from
  ansible_playbook
where
  become = 1
  and (
    become_user is null
    or become_user = 'root'
  );

Schema for ansible_playbook

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
become	boolean		Controls if privilege escalation is used or not on task execution. If true, privilege escalation is activated.
become_flags	text		A string of flag(s) to pass to the privilege escalation program when become is true.
become_method	text		Specifies which method of privilege escalation to use (such as sudo or su).
become_user	text		User that you 'become' after using privilege escalation.
check_mode	boolean		A boolean that controls if a task is executed in 'check' mode.
collections	jsonb		A section with tasks that are treated as handlers, these won't get executed normally, only when notified after each section of tasks is complete.
debugger	text		Enable debugging tasks based on state of the task result. Allowed values are: always, never, on_failed, on_unreachable, on_skipped.
diff	boolean		Toggle to make tasks return 'diff' information or not.
environment	jsonb		A dictionary that gets converted into environment vars to be provided for the task upon execution.
force_handlers	boolean		Will force notified handler execution for hosts even if they failed during the play.
gather_facts	boolean		A boolean that controls if the play will automatically run the 'setup' task to gather facts for the hosts.
gather_subset	jsonb		Allows you to pass subset options to the fact gathering plugin controlled by gather_facts.
handlers	jsonb		A section with tasks that are treated as handlers, these won't get executed normally, only when notified after each section of tasks is complete.
hosts	text		A list of groups, hosts or host pattern that translates into a list of hosts that are the play's target.
ignore_errors	boolean		Boolean that allows you to ignore task failures and continue with play.
ignore_unreachable	boolean		Boolean that allows you to ignore task failures due to an unreachable host and continue with the play.
max_fail_percentage	bigint		It can be used to abort the run after a given percentage of hosts in the current batch has failed.
module_defaults	jsonb		Specifies default parameter values for modules.
name	text		The name of the playbook.
no_log	boolean		Boolean that controls information disclosure.
order	text		Controls the sorting of hosts as they are used for executing the play. Possible values are inventory (default), sorted, reverse_sorted, reverse_inventory and shuffle.
path	text	=	Path to the file.
post_tasks	jsonb		A list of tasks to execute after the tasks section.
pre_tasks	jsonb		A list of tasks to execute before roles.
remote_user	text		User used to log into the target via the connection plugin.
roles	jsonb		The list of roles to be imported into the play.
run_once	boolean		Boolean that will bypass the host loop, forcing the task to attempt to execute on the first host available and afterwards apply any results and facts to all active hosts in the same batch.
serial	bigint		Explicitly define how Ansible batches the execution of the current play on the play's target.
strategy	text		Allows you to choose the connection plugin to use for the play.
tags	text		Tags applied at the level of play.
tasks	jsonb		The list of tasks to execute in the play.
throttle	bigint		Limit number of concurrent task runs on task, block and playbook level.
timeout	bigint		Time limit for task to execute in, if exceeded Ansible will interrupt and fail the task.
vars	jsonb		The dictionary or map of variables.
vars_files	jsonb		A list of files that contain vars to include in the play.
vars_prompt	jsonb		A list of variables to prompt for.

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)" -- ansible

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

steampipe_export_ansible --config '<your_config>' ansible_playbook