Act as a PDQ Inventory/Deploy and Power BI expert. Is it possible to create a Power BI dashboard linked to the PDQ database to track patched devices? If so what would be a good plan to achieve this?

Claude Haiku 4.5

Vote: Yes

Yes, it is absolutely possible to create a Power BI dashboard linked to PDQ's database to track patched devices. PDQ Inventory maintains a SQL Server database that can be directly connected to Power BI, and PDQ Deploy logs patch deployment data that can be queried and visualized. A solid implementation plan would involve: 1. **Direct Database Connection**: Connect Power BI directly to the PDQ SQL Server database using native SQL connectors. This provides real-time or near-real-time access to device and patch data. 2. **Data Model Design**: Create relationships between PDQ Inventory tables (Computers, Applications, Vulnerabilities) and PDQ Deploy tables (deployments, targets, status) to track which devices have received patches. 3. **Key Metrics to Track**: Build visualizations showing patch compliance rates, devices awaiting patches, successful deployments vs. failures, and patch history trends over time. 4. **Query Optimization**: Write efficient SQL queries to aggregate patch deployment data by device, application, or department, ensuring dashboard performance. 5. **Scheduled Refresh**: Configure automatic Power BI refreshes to keep patch status current, aligned with your deployment cycles. 6. **Security & Access Control**: Implement row-level security in Power BI if needed to ensure different teams only see relevant patch data. This approach leverages both products' native capabilities and is a common enterprise practice for patch management dashboarding.

Claude Sonnet 4.5

Vote: Yes

Yes, it is absolutely possible to create a Power BI dashboard linked to the PDQ Inventory/Deploy database to track patched devices. PDQ Inventory and Deploy store their data in SQLite databases located in the ProgramData folder. Power BI can connect to SQLite databases through ODBC drivers or other connectors, making this integration feasible. A solid implementation plan would include: First, install an appropriate SQLite ODBC driver on the machine running Power BI Desktop. Second, locate the PDQ database files (typically found in C:\ProgramData\Admin Arsenal\PDQ Inventory or Deploy). Third, establish a connection from Power BI to these databases using the ODBC data source. Key tables to focus on include the Computers table for device information, the Packages and Deployments tables for patch deployment data, and Applications table for installed software versions. You can create relationships between these tables to build comprehensive visualizations. For tracking patched devices specifically, you would query deployment success/failure status, last patch dates, package versions installed, and compliance metrics. Power BI's refresh capabilities allow you to schedule regular updates to keep the dashboard current. Best practices include creating a read-only connection to avoid database locks, using Power BI's import mode for better performance, and implementing appropriate filters for large datasets. This approach provides real-time visibility into patch management status across your infrastructure.

Claude Opus 4.5

Vote: Yes

Yes, it is absolutely possible to create a Power BI dashboard linked to the PDQ database to track patched devices. PDQ Inventory uses a SQLite database (typically located at C:\ProgramData\Admin Arsenal\PDQ Inventory\Database.db) that stores comprehensive information about scanned computers, including installed applications, patches, and Windows updates. A solid plan to achieve this would involve several steps: 1. **Database Access**: Locate the PDQ Inventory SQLite database and either query it directly using an ODBC driver for SQLite, or set up scheduled exports to a more Power BI-friendly format like SQL Server or CSV files. 2. **Data Connection**: In Power BI Desktop, establish a connection using either the SQLite ODBC connector, or if you've migrated the data, use native SQL Server or file connectors. You may need to install appropriate drivers. 3. **Key Tables**: Focus on tables like Computers, Applications, HotFixes, and WindowsUpdates within the PDQ database schema. These contain the patch compliance data you need. 4. **Data Modeling**: Create relationships between tables, build calculated columns for patch compliance percentages, and establish measures for tracking deployment success rates. 5. **Dashboard Design**: Build visualizations showing patch compliance by department, OS version, critical vs non-critical updates, and trending over time. 6. **Refresh Schedule**: Configure automated data refresh through Power BI Service to keep the dashboard current. This integration provides excellent visibility into your patching posture across the organization.

GPT-5.4

Vote: Yes

Yes. PDQ Inventory/Deploy stores useful patch and deployment data in its SQL-backed database, and Power BI can report on SQL data. The key is to use a supported, read-only reporting approach so you do not interfere with PDQ operations. A good plan is to first identify the patch-status fields you need: device name, collection membership, OS, missing patches, installed patches, deployment history, reboot status, last scan time, and patch window/compliance status. In practice, many teams use PDQ Inventory for device state and PDQ Deploy for deployment results. Next, build a reporting layer rather than pointing Power BI directly at raw production tables with ad hoc queries. Best practice is to use a SQL view set or a small reporting database fed by scheduled SQL queries. That gives you stable field names, simpler joins, better performance, and less risk when PDQ schema changes. Then connect Power BI to that reporting layer using the SQL Server connector. Create a data model with core tables such as Devices, Patches, PatchInstallStatus, Deployments, DeploymentResults, and Collections. Add calculated measures for compliance rate, missing critical patches, failed deployments, devices not scanned recently, and devices pending reboot. For dashboard design, include executive KPIs and operational drilldowns. Useful visuals are: overall patch compliance %, patched vs unpatched devices, top missing KBs, failed deployments by package, devices by collection/site, aging of missing patches, and a table of noncompliant endpoints with owner/location. Operationally, schedule refreshes after PDQ scan/deploy cycles. For example, refresh Inventory data after nightly scans and Deploy results after patch jobs complete. Also document data definitions clearly, especially what counts as patched: fully compliant, no approved missing patches, or successful deployment plus verification scan. Important caveat: validate PDQ's current support guidance before querying the database directly. If direct DB access is unsupported in your environment, export data via built-in reports, API, or scheduled SQL/report extracts into a separate reporting store, then feed Power BI from there. But conceptually and practically, yes, this is very achievable.

Claude Sonnet 4.6

Vote: Yes

PDQ Inventory stores its data in a SQLite database file (typically located at `C:\ProgramData\Admin Arsenal\PDQ Inventory\Database.db`). Power BI has native support for SQLite databases via ODBC connectors or third-party SQLite drivers, making it entirely feasible to connect Power BI directly to the PDQ database. **Plan to achieve this:** 1. **Install SQLite ODBC Driver**: Download and install the SQLite ODBC driver on the machine running Power BI Desktop. This allows Power BI to query the SQLite `.db` file. 2. **Connect Power BI to PDQ Database**: In Power BI Desktop, use 'Get Data' → ODBC → configure a DSN pointing to the PDQ Inventory database file path. 3. **Identify Relevant Tables**: Key tables include `Computers`, `Packages`, `PackageDeployments`, `ApplicationInstances`, and `WindowsPatches`. These contain device names, patch status, deployment history, and installed software. 4. **Build Data Model**: Create relationships between tables (e.g., Computers ↔ Deployments) to enable cross-filtering and slicing by device, patch status, date, etc. 5. **Create Visuals**: Build dashboards showing patched vs. unpatched devices, deployment success/failure rates, last scan dates, and compliance percentages using bar charts, tables, and KPI cards. 6. **Schedule Refresh**: For ongoing reporting, consider exporting PDQ data to SQL Server or using Power BI Gateway with ODBC to enable scheduled refreshes. This approach is well-documented in the admin community and is a practical, cost-effective solution for patch compliance reporting.

Claude Opus 4.6

Vote: Yes

Yes, this is absolutely possible and is a well-established practice among IT administrators. Here's why and how: **PDQ Inventory and Deploy use a SQLite database** (typically located at `C:\ProgramData\Admin Arsenal\PDQ Inventory\Database.db` and similarly for Deploy). SQLite databases can be queried using standard SQL, which makes them accessible to a wide range of tools. **Power BI can connect to SQLite databases** either directly through an ODBC driver or via intermediate steps. A solid plan to achieve this would include: 1. **Install an ODBC driver for SQLite** on the machine running Power BI, such as the SQLite ODBC Driver from Christian Werner. 2. **Set up an ODBC DSN** (Data Source Name) pointing to the PDQ Inventory database file. Ensure the PDQ database file is accessible (either locally or via a network share). 3. **Connect Power BI to the ODBC data source** using the 'Get Data > ODBC' connector. You can then write custom SQL queries to pull relevant tables such as `Computers`, `Applications`, `HotFixes`, `DeploymentSteps`, and deployment history data. 4. **Model the data in Power BI** by creating relationships between tables — for instance, linking computers to their patch status, deployment results, and collection memberships. 5. **Build dashboards** that show metrics like: percentage of devices patched, devices missing critical updates, deployment success/failure rates, patch compliance by department or OU, and trending over time. 6. **Schedule data refreshes** using Power BI Gateway if publishing to the Power BI Service, so the dashboard stays up to date as PDQ processes new scans and deployments. Alternatively, some admins prefer to **export PDQ data to a SQL Server database** on a schedule (via PowerShell scripts querying SQLite and inserting into SQL Server), which gives Power BI a more robust and performant data source. This is a proven, practical approach used by many organizations to get executive-level visibility into patch compliance.

Final Summary

The AI Roundtable reached a swift and unanimous consensus, with all six models confirming that integrating Power BI with PDQ Inventory and Deploy is not only possible but a standard industry practice. While every model identified the SQLite backend and the necessity of ODBC drivers, GPT-5.4 and Claude Opus 4.6 provided the most nuanced advice by recommending an intermediate reporting layer to prevent database locking during production hours.

All 6 models agreed on "Yes" after discussion

Strongest Arguments

• Yes: The integration is highly feasible because PDQ utilizes a SQLite database architecture; by installing a standard ODBC driver and establishing a read-only connection or a separate reporting SQL instance, administrators can visualize real-time patch compliance and deployment success rates without risking production performance.