Last week, we unlocked the "Golden Rule" of safe automation: idempotency.  You now know that a reliable playbook leaves your system in the desired state, regardless of how many times you run it.  That confidence allows you to do more than just report; it lets you change things safely.

But what if your automation could adapt? 

What if it knew the difference between a dev server and a production server, or whether a server had 8GB or 1TB of RAM, and adjusted its actions accordingly?

As a SQL Server DBA, you constantly deal with different server configurations.  One server might have a specific patch level, another might be a clustered instance, and yet another might be running a different OS version.  Your existing scripts likely need manual tweaks for each environment.  This means more effort, more room for error, and less time for strategic work.  You're stuck on "one-off" solutions when you need a single, intelligent system.

This friction prevents you from building truly scalable automation.

This is where the true power of an Automation Architect shines.  Instead of hardcoding every detail, an Architect designs playbooks that discover the server's environment and adapt.  The key to this intelligence is a concept called Ansible Facts.  Ansible, before doing anything, and by default, gathers all the relevant information about your target server (managed node).

What Are Ansible Facts?

Ansible includes a built-in module called setup (ansible.builtin.setup).  Its primary job is to gather "facts" about the remote host.  These facts included everything from the operating system version, memory, network interfaces, CPU count, and much more.  

Let's dive in and see how Ansible Facts can make your automation significantly smarter and more flexible.

Step 1: Gathering and Viewing Facts

To see these facts in action, create a new playbook called playbook_gatherFacts.yml.

---
- name: Gather facts about SQL Server host
  hosts: sqlservers
  gather_facts: true

  tasks:
    - name: Display gathered facts
      ansible.builtin.debug:
        var: ansible_facts

Run this playbook against one of your servers:

ansible-playbook playbook_gatherFacts.yml -i inventory.ini -u YourUserName --ask-pass

At first glance, this data dump can feel overwhelming. To help you navigate it, here is a categorized breakdown of the most common and useful facts you'll find. Think of it as a helpful map or glossary for your server's hardware, OS, and network configuration. Skim through the categories to see the incredible range of information that is now available for your automation.

🖥️ System & Hardware

This category covers the physical or virtual hardware specifications of the machine.

• architecture: The system's processor architecture (e.g., 64-bit).

• architecture2: A secondary architecture identifier (e.g., x86_64).

• bios_date: The release date of the system's BIOS.

• bios_version: The version string of the system's BIOS.

• memfree_mb: The amount of free physical RAM, in megabytes.

• memtotal_mb: The total amount of physical RAM, in megabytes.

• pagefilefree_mb: The amount of free page file space, in megabytes.

• pagefiletotal_mb: The total size of the page file, in megabytes.

• processor: A detailed list of all processor cores and their descriptions.

• processor_cores: The number of physical CPU cores.

• processor_count: The total number of logical processors.

• processor_threads_per_core: The number of threads per CPU core.

• processor_vcpus: The number of virtual CPUs allocated to the guest.

• product_name: The model name of the system, as reported by the BIOS.

• product_serial: The serial number of the system.

• product_uuid: The universally unique identifier (UUID) for the system.

• swaptotal_mb: The total amount of swap space, in megabytes.

• system: The base operating system type (e.g., Win32NT).

• system_description: A description of the system provided by the owner.

• system_vendor: The manufacturer of the system (e.g., VMware, Inc.).

• uptime_seconds: The total number of seconds the system has been running.

💿 Operating System

These facts relate to the software, configuration, and state of the operating system itself.

• distribution: The full name of the operating system distribution.

• distribution_major_version: The major version number of the OS.

• distribution_version: The full version string of the OS.

• kernel: The version of the operating system kernel.

• lastboot: The timestamp of the last system boot.

• machine_id: The unique security identifier (SID) of the machine.

• os_family: The family of the operating system (e.g., Windows).

• os_install_date: The timestamp when the OS was installed.

• os_installation_type: The type of OS installation (e.g., Server).

• os_name: The full name of the operating system.

• os_product_type: The role of the OS installation (e.g., server).

• powershell_version: The installed version of PowerShell.

• reboot_pending: A boolean value indicating if a system reboot is required.

• win_rm_certificate_expires: The expiration date of the WinRM listener certificate.

• win_rm_certificate_thumbprint: The thumbprint of the WinRM listener certificate.

🌐 Networking & Domain

This section covers network interfaces, addresses, and domain membership.

• domain: The Active Directory domain the machine belongs to.

• fqdn: The server's Fully Qualified Domain Name (e.g., SQL1.SANDBOX.LOCAL).

• hostname: The short name of the server, without the domain.

• interfaces: A detailed list of all network interfaces and their configurations.

• ip_addresses: A simple list of all IP addresses assigned to the machine.

• netbios_name: The NetBIOS name of the machine.

• nodename: The full hostname, which often matches the FQDN.

• windows_domain: The Active Directory domain the machine belongs to.

• windows_domain_member: A boolean value indicating if the machine is joined to a domain.

• windows_domain_role: The machine's role within the domain (e.g., Member server).

👤 User & Environment

Facts related to the user context and system environment variables.

• env: A dictionary of all system environment variables.

• owner_contact: The contact information for the system owner.

• owner_name: The name of the system owner.

• user_dir: The home directory path for the current user.

• user_gecos: The GECOS (General Electric Comprehensive Operating Supervisor) field for the user.

• user_id: The username of the currently logged-in user.

• user_sid: The security identifier (SID) of the current user.

☁️ Virtualization

These facts identify if the machine is a virtual guest and on what platform it's running.

• virtualization_role: The role of the machine in virtualization (e.g., guest).

• virtualization_type: The virtualization platform (e.g., VMware).

✨ Ansible Meta

Facts related to how Ansible itself is operating.

• gather_subset: The subset of facts that Ansible was instructed to gather.

• module_setup: A boolean indicating if the setup module ran successfully.

Step 2: Using Facts to Make Decisions (Conditional Logic)

Now, we can also use these facts to make your playbooks dynamic.  Imagine you need to apply an update to SQL Server.  You'll need to check for any pending reboots first.  By checking the pending_reboot fact, you could build a playbook to first restart the server prior to attempting to install the update (and skip the potential installation failure).

Ansible lets you use when conditions to apply tasks only when certain facts are true.

Let's modify our playbook_checkVersion.yml playbook from week 2 to demonstrate a quick example.  We'll add two tasks; one task will run if the Ansible fact, pending_reboot, is true, and the other will run if pending_reboot is false.

---
- name: Check SQL Server version
  hosts: sqlservers
  gather_facts: true

  tasks:
    - name: Set the server_instance variable conditionally
      ansible.builtin.set_fact:
        server_instance: "{% if 'DEFAULT' in sql_instance_name %}{{ inventory_hostname }}{% else %}{{ inventory_hostname }}\\{{ sql_instance_name }}{% endif %}"

    - name: Run version query
      ansible.windows.win_powershell:
        script: |
          [CmdletBinding()]
            param(
              [Parameter(Mandatory=$true)]
              [string]$ServerInstance
            )
            (Invoke-SqlCmd -ServerInstance "$ServerInstance" `
            -Query "SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion" -encrypt optional).ProductVersion.ToString()

            $Ansible.Changed = $false
        error_action: Stop
        parameters:
          ServerInstance: "{{ server_instance }}"
      register: sql_version_result

    - name: Display the version
      ansible.builtin.debug:
        msg: "The version for {{ server_instance }} is: {{ sql_version_result.output[0] }}"

    # - name: Display gathered gather_facts
    #   ansible.builtin.debug:
    #     var: ansible_facts

    - name: This task only runs if a reboot is pending
      ansible.builtin.debug:
        msg: "A reboot is pending on this host."
      when: ansible_facts['reboot_pending']

    - name: This task only runs if a reboot is NOT pending
      ansible.builtin.debug:
        msg: "No reboot is pending on this host."
      when: not ansible_facts['reboot_pending']

Run this updated playbook.

ansible-playbook playbook_checkVersion.yml -i inventory.ini -u [email protected] --ask-pass

Here, you'll notice a few things.

• The task using the condition "when: ansible_facts['reboot_pending']" is skipped on SQL2.
• The task using the condition "when: not ansible_facts['reboot_pending'" is skipped on SQL1, SQL3, and SQL4.  Because each of these servers has a reboot pending.

In this example, we could modify the "This task only runs if a reboot is pending" task to restart the server and then proceed with the patch once the server is back online.

Next Steps

Explore the ansible_facts output from your playbook_gatherFacts.yml playbook.  Think about how you could use this information to:

• Apply different security configurations based on ansible_os_name.
• Manage Windows Server Failover Clusters differently from standalone instances.

This is the power to build truly resilient and adaptable automation.  You're not limited to the default facts returned by the ansible.builtin.setup module.  Setting your own facts using ansible.builtin.set_fact can be used as well (as shown in the playbook_checkVersion.yml example).

Next week, we'll put these concepts to a real test: managing different sets of variables for different environments (Dev, QA, Prod) using Group Vars.  This is essential for building a truly scalable automation framework.

By using Ansible Facts, you avoid the biggest failures of brittle automation: hardcoding values that break when environments change, or applying changes to the wrong server because you couldn't dynamically detect its role.  

You're building automation that is not only safe but also intelligently adapts.