A solution to create custom reports using DBAtools Powershell Module.

Solution to create custom reports using DBAtools.

During my recent audit, I enjoyed examining fifty SQL servers, each hosting three instances. I could streamline the process by utilising a specific command, creating a command line for each server and instance, and extracting the data into an Excel format in the C: REPORTS folder.

The prerequisite for this task is installing DBAtools on a server with access to all the SQL servers. Once the initial setup is completed, generating ad-hoc reports becomes swift, taking just minutes. However, be prepared for longer durations if numerous SQL instances exist on the same server.

This introductory guide aims to illuminate the efficient methods and tools used to audit and report SQL servers, ensuring you can replicate this streamlined process in your own environment. Stay tuned for detailed steps and insights!

Command used.

Invoke-DbaDiagnosticQuery -SqlInstance SERVERNAME\SQLINSTANCE -QueryName "Database Filenames and Paths" | Export-DbaDiagnosticQuery -ConvertTo Excel -Path c:\REPORTS\SQLSERVERNAME\SQLINSTANCE -NoPlanExport -NoQueryExport

The same command can be used to generate custom reports using DBATools by exchanging the

variable in red Database Filenames and Paths with one from the list below.

Version Info

Core Counts

Server Properties

Configuration Values

Process Memory

SQL Server Services Info

Last Backup By Database

SQL Server Agent Jobs

SQL Server Agent Alerts

Windows Info

SQL Server NUMA Info

System Memory

AlwaysOn AG Cluster

AG Status

System Manufacturer

BIOS Date

Hardware Info

Processor Description

TempDB Data Files

Database Filenames and Paths

Volume Info

Drive Level Latency

IO Latency by File

RG Resource Pools

Database Properties

Missing Indexes All Databases

VLF Counts

CPU Usage by Database

IO Usage By Database

Total Buffer Usage by Database

Top Waits

Connection Counts by IP Address

Avg Task Counts

CPU Utilization History

Top Worker Time Queries

PLE by NUMA Node

Memory Grants Pending

Memory Clerk Usage

Ad hoc Queries

Top Logical Reads Queries

Top Avg Elapsed Time Queries

Once you have created a custom script with all the servers listed, you can use Notepad to edit the file, using the replace function