singlecheeze/Database Reporting Via Ansible.md

## Database Reporting Via Ansible.md

      
    Raw
  

              Database Reporting Via Ansible.md
            
          
    Links:

https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_info_module.html
https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_info_module.html#ansible-collections-community-mysql-mysql-info-module
https://docs.ansible.com/ansible/latest/playbook_guide/playbooks_vars_facts.html
May need to set local connections to databases to "trust":

https://access.redhat.com/documentation/fr-fr/red_hat_enterprise_linux/9/html/configuring_and_using_database_servers/configuring-postgresql_using-postgresql
https://stackoverflow.com/questions/25751085/postgresql-failing-peer-authentication-with-ansible
Create a report of installed packages in Ansible:

https://stackoverflow.com/questions/72808138/create-a-report-of-installed-packages-in-ansible
Hosts File
[mysql]
rhel8dummy1.localdomain

[postgresql]
rhel8dummy2.localdomain
172.16.1.175
Ansible.cfg
[defaults]
host_key_checking = False
inventory = hosts
gathering = explicit
retry_files_enabled = False # Do not create them
collections_paths = /home/dave/.ansible/collections/ansible_collections

[paramiko_connection]
host_key_auto_add = True
inventory-dbs.yaml
---
- name: Build database inventory report
  hosts: all
#  become: true
  gather_facts: true

  vars:
    mysql_desired_version: 9.0
    psql_desired_version: 12.0
    file_path: ./inventory_report_dbs.html

  tasks:
    - name: Get MySQL details
      block:
      - name: Get MySQL version
        community.mysql.mysql_info:
  #        login_unix_socket: /var/lib/mysql/mysql.sock
  #        login_unix_socket: /var/run/mysqld/mysqld.sock
  #        login_host: rhel8dummy1.localdomain
  #        login_user: "root"
  #        login_password: "Welcome11"
          filter: version
        register: mysql_version

#      - debug:
#          msg:
#          - "MySQL Version: {{ mysql_version['version']['full'] }}"
      when: "'mysql' in group_names"


    - name: Get Postgres details
      block:
      - name: Get Postgres version
        community.postgresql.postgresql_info:
         filter: ver*,ext*
        register: psql_version

#      - debug:
#          msg:
#            - "PostgreSQL Version: {{ psql_version['version']['full'] }}"
      when: "'postgresql' in group_names"


#    - debug:
#        msg:
#        - "Hostname: {{ ansible_facts['hostname'] }}"
#        - "IPs: {{ ansible_facts['all_ipv4_addresses'] }}"
#        - "Cores: {{ansible_facts['processor_cores'] }}"
#        - "Proc Type: {{ ansible_facts['processor'] }}"
#        - "Distro: {{ ansible_facts['distribution'] }} {{ ansible_facts['distribution_version'] }}"
#        - "Kernel: {{ ansible_facts['kernel'] }}"

  post_tasks:
    - name: Create HTML report
      template:
        src: report-dbs.j2
        dest: "{{ file_path }}"
      delegate_to: localhost
      run_once: true
report-dbs.j2
<!DOCTYPE html>
<html lang="en">
    <head>
      <link rel="stylesheet" href="css/main.css">
        <title>Database Inventory Report</title>
    </head>
    <body>
        <h1 style="margin: 0; padding: 0;">Database Inventory Report</h1>
{#        {% for host in groups['all'] %}#}
{#            {{ hostvars[host] }}#}
{#        {% endfor %}#}
        <table style="margin: 0;">
            <thead>
                <tr>
                    <th>Hostname</th>
                    <th>IPs</th>
                    <th>Distro</th>
                    <th>Kernel</th>
                    <th>MySQL Version</th>
                    <th>PostgreSQL Version</th>
                    <th>IPs</th>
                    <th>RAM (MB)</th>
                    <th>Cores</th>
                    <th>Proc Type</th>
                </tr>
            </thead>
            <tbody>
                {% for host in groups['all'] %}
                    <tr>
                        <td>{{ hostvars[host]['inventory_hostname'] }}</td>
                        <td>{{ hostvars[host].ansible_facts["all_ipv4_addresses"]}}</td>
                        <td>{{ hostvars[host].ansible_facts['distribution'] }} {{ hostvars[host].ansible_facts['distribution_version'] }}</td>
                        <td>{{ hostvars[host].ansible_facts['kernel'] }}</td>

                        <td>
                            {% if 'mysql' in hostvars[host].group_names and 'version' in hostvars[host].mysql_version %}
                                <span {% if hostvars[host].mysql_version['version']['full'] | float <= mysql_desired_version %}
                                    style="background:yellow;"
                                {% endif %}>
                                {{ hostvars[host].mysql_version['version']['full'] | float }}
                                </span>
                            {% endif %}
                        </td>

                        <td>
                            {% if 'postgresql' in hostvars[host].group_names and 'version' in hostvars[host].psql_version %}
                                <span {% if hostvars[host].psql_version['version']['full']  | float <= psql_desired_version %}
                                    style="background:yellow;"
                                {% endif %}>
                                {{ hostvars[host].psql_version['version']['full'] | float }}
                                </span>
                            {% endif %}
                        </td>

                        <td>{{ hostvars[host].ansible_facts['all_ipv4_addresses'] }}</td>
                        <td>{{ hostvars[host].ansible_facts['memory_mb']['real']['total'] }}</td>
                        <td>{{ hostvars[host].ansible_facts['processor_cores'] }}</td>
                        <td>{{ hostvars[host].ansible_facts['processor'] }}</td>
                    </tr>
                {% endfor %}
            </tbody>
        </table>
    </body>
</html>
Run from command line:
ansible-playbook inventory-dbs.yml

Expected Output:
dave@t14s:~/ansible_inventory_report_rhel$ ansible-playbook inventory-dbs.yml

PLAY [Build database inventory report] *************************************************************************************************************************************************

TASK [Gathering Facts] *****************************************************************************************************************************************************************
ok: [172.16.1.175]
ok: [rhel8dummy2.localdomain]
ok: [rhel8dummy1.localdomain]

TASK [Get MySQL version] ***************************************************************************************************************************************************************
skipping: [rhel8dummy2.localdomain]
skipping: [172.16.1.175]
ok: [rhel8dummy1.localdomain]

TASK [Get Postgres version] ************************************************************************************************************************************************************
skipping: [rhel8dummy1.localdomain]
ok: [172.16.1.175]
ok: [rhel8dummy2.localdomain]

TASK [Create HTML report] **************************************************************************************************************************************************************
ok: [rhel8dummy1.localdomain -> localhost]

PLAY RECAP *****************************************************************************************************************************************************************************
172.16.1.175               : ok=2    changed=0    unreachable=0    failed=0    skipped=1    rescued=0    ignored=0   
rhel8dummy1.localdomain    : ok=3    changed=0    unreachable=0    failed=0    skipped=1    rescued=0    ignored=0   
rhel8dummy2.localdomain    : ok=2    changed=0    unreachable=0    failed=0    skipped=1    rescued=0    ignored=0