DBATools

query.txt

IDQuery,QueryName,DBSpecific,Description
1,Version Info,FALSE,SQL and OS Version information for current instance
2,Core Counts,FALSE,"Get socket, physical core and logical core count from the SQL Server Error log."
3,Server Properties,FALSE,Get selected server properties
4,Configuration Values,FALSE,Get instance-level configuration values for instance
5,Global Trace Flags,FALSE,Returns a list of all global trace flags that are enabled
6,IFI Status,FALSE,Returns status of instant file initialization
7,Process Memory,FALSE,SQL Server Process Address space info
8,SQL Server Services Info,FALSE,SQL Server Services information
9,Last Backup By Database,FALSE,Last backup information by database
10,SQL Server Agent Jobs,FALSE,Get SQL Server Agent jobs and Category information
11,SQL Server Agent Alerts,FALSE,Get SQL Server Agent Alert Information
12,Host Info,FALSE,Host information
13,SQL Server NUMA Info,FALSE,SQL Server NUMA Node information
14,System Memory,FALSE,Good basic information about OS memory amounts and state
15,Cluster Node Properties,FALSE,Get information about your cluster nodes and their status
16,AlwaysOn AG Cluster,FALSE,Get information about any AlwaysOn AG cluster this instance is a part of
17,AlwaysOn AG Status,FALSE,Good overview of AG health and status
18,Hardware Info,FALSE,Hardware information from SQL Server 2017
19,System Manufacturer,FALSE,Get System Manufacturer and model number from SQL Server Error log
20,PVSCSI Driver Parameters,FALSE,Get pvscsi info from Windows Registry
21,BIOS Date,FALSE,Get BIOS date from Windows Registry
22,Processor Description,FALSE,Get processor description from Windows Registry
23,BPE Configuration,FALSE,See if buffer pool extension (BPE) is enabled
24,BPE Usage,FALSE,Look at buffer descriptors to see BPE usage by database
25,Memory Dump Info,FALSE,"Get information on location, time and size of any memory dumps from SQL Server "
26,Suspect Pages,FALSE,Look at Suspect Pages table
27,TempDB Data Files,FALSE,Get number of data files in tempdb database
28,Database Filenames and Paths,FALSE,File names and paths for all user and system databases on instance
29,Fixed Drives,FALSE,Drive information for all fixed drives visible to the operating system
30,Volume Info,FALSE,Volume info for all LUNS that have database files on the current instance
31,Drive Level Latency,FALSE,Drive level latency information
32,IO Latency by File,FALSE,"Calculates average stalls per read, per write, and per total input/output for each database file "
33,IO Warnings,FALSE,Look for I/O requests taking longer than 15 seconds in the six most recent SQL Server Error Logs
34,RG Resource Pools,FALSE,Resource Governor Resource Pool information
35,Database Properties,FALSE,"Recovery model, log reuse wait description, log file size, log usage size "
36,Missing Indexes All Databases,FALSE,Missing Indexes for all databases by Index Advantage
37,VLF Counts,FALSE,Get VLF Counts for all databases on the instance
38,CPU Usage by Database,FALSE,Get CPU utilization by database
39,IO Usage By Database,FALSE,Get I/O utilization by database
40,Total Buffer Usage by Database,FALSE,Get total buffer usage by database for current instance
41,Version Store Space Usage,FALSE,Get tempdb version store space usage by database
42,Top Waits,FALSE,Isolate top waits for server instance since last restart or wait statistics clear
43,Connection Counts by IP Address,FALSE,Get a count of SQL connections by IP address
44,Avg Task Counts,FALSE,Get Average Task Counts (run multiple times)
45,Detect Blocking,FALSE,Detect blocking (run multiple times)
46,CPU Utilization History,FALSE,Get CPU Utilization History for last 256 minutes (in one minute intervals)
47,Top Worker Time Queries,FALSE,Get top total worker time queries for entire instance
48,PLE by NUMA Node,FALSE,Page Life Expectancy (PLE) value for each NUMA node in current instance
49,Memory Grants Pending,FALSE,Memory Grants Pending value for current instance
50,Memory Clerk Usage,FALSE,Memory Clerk Usage for instance
51,Ad hoc Queries,FALSE,"Find single-use, ad-hoc and prepared queries that are bloating the plan cache "
52,Top Logical Reads Queries,FALSE,Get top total logical reads queries for entire instance
53,Top Avg Elapsed Time Queries,FALSE,Get top average elapsed time queries for entire instance
54,UDF Stats by DB,TRUE,Look at UDF execution statistics
55,File Sizes and Space,TRUE,Individual File Sizes and space available for current database
56,Log Space Usage,TRUE,Log space usage for current database
57,Last VLF Status,TRUE,Status of last VLF for current database
58,Database-scoped Configurations,TRUE,Get database scoped configuration values for current database
59,IO Stats By File,TRUE,I/O Statistics by file for the current database
60,Query Execution Counts,TRUE,Get most frequently executed queries for this database
61,SP Execution Counts,TRUE,Top Cached SPs By Execution Count
62,SP Avg Elapsed Time,TRUE,Top Cached SPs By Avg Elapsed Time
63,SP Worker Time,TRUE,Top Cached SPs By Total Worker time. Worker time relates to CPU cost
64,SP Logical Reads,TRUE,Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure
65,SP Physical Reads,TRUE,Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure
66,SP Logical Writes,TRUE,Top Cached SPs By Total Logical Writes
67,Top IO Statements,TRUE,Lists the top statements by average input/output usage for the current database
68,Bad NC Indexes,TRUE,Possible Bad NC Indexes (writes > reads)
69,Missing Indexes,TRUE,Missing Indexes for current database by Index Advantage
70,Missing Index Warnings,TRUE,Find missing index warnings for cached plans in the current database
71,Buffer Usage,TRUE,"Breaks down buffers used by current database by object (table, index) in the buffer cache "
72,Table Sizes,TRUE,"Get Table names, row counts, and compression status for clustered index or heap "
73,Table Properties,TRUE,Get some key table properties
74,Statistics Update,TRUE,When were Statistics last updated on all indexes?
75,Volatile Indexes,TRUE,Look at most frequently modified indexes and statistics
76,Index Fragmentation,TRUE,Get fragmentation info for all indexes above a certain size in the current database
77,Overall Index Usage - Reads,TRUE,Index Read/Write stats (all tables in current DB) ordered by Reads
78,Overall Index Usage - Writes,TRUE,Index Read/Write stats (all tables in current DB) ordered by Writes
79,XTP Index Usage,TRUE,Get in-memory OLTP index usage
80,Columnstore Index Physical Stat,TRUE,Look at Columnstore index physical statistics
81,Lock Waits,TRUE,Get lock waits for current database
82,UDF Statistics,TRUE,Look at UDF execution statistics
83,QueryStore Options,TRUE,Get QueryStore Options for this database
84,High Aggregate Duration Queries,TRUE,Get highest aggregate duration queries over last hour
85,High Aggregate CPU Queries,TRUE,Get highest aggregate CPU time queries over last hour
86,Input Buffer,TRUE,Get input buffer information for the current database
87,Resumable Index Rebuild,TRUE,Get any resumable index rebuild operation information
88,Automatic Tuning Options,TRUE,Get database automatic tuning options
89,Recent Full Backups,TRUE,Look at recent Full backups for the current database

#########################################################################

$file = Import-Csv C:\ORAMIX\servidores.TXT
$file2 = Import-Csv C:\ORAMIX\query.txt


#Get-Content -Path C:\CONTISYSTEMS\servidores.txt | SELECT INSTANCIA
Clear-Host

#Client Name

$Client = Read-Host -Prompt 'Insert Client Name'

#Create Directory
$Path_Instance = 'C:\ORAMIX\'+$Client.ToUpper()+'\IN\MapeamentoInstanceOnlyCSV\'
$Path_Database = 'C:\ORAMIX\'+$Client.ToUpper()+'\IN\MapeamentoDatabaseOnlyCSV\'


if (-not(Test-Path -Path $Path_Instance -PathType Container))
{
    New-Item -ItemType directory -Path $Path_Instance
}
if (-not(Test-Path -Path $Path_Database -PathType Container))
{
    New-Item -ItemType directory -Path $Path_Database
}

$cred = Get-Credential ff.oramix


ForEach ($item in $file)
{
    ForEach ($item2 in $file2)
    {
        $Path_Instance_GET = $Path_Instance + $item2.QueryName.Replace(" ","_").ToLower()
        $Path_Database_GET = $Path_Database + $item2.QueryName.Replace(" ","_").ToLower()
        if ($item2.DBSpecific -eq 'FALSE')
        {
            #INSTANCE

            Invoke-DbaDiagnosticQuery -SqlCredential $cred -SqlInstance $item.instance -InstanceOnly -QueryName $item2.QueryName -Verbose 4>> $Path_Instance+"Save-Verbose.txt" | Export-DbaDiagnosticQuery -NoPlanExport -NoQueryExport -Path $Path_Instance_GET

        }
        else
        {
            #DATABASE

            Invoke-DbaDiagnosticQuery -SqlCredential $cred -SqlInstance $item.instance -DatabaseSpecific -QueryName $item2.QueryName -Verbose 4>> "C:\ORAMIX\MapeamentoDatabaseOnlyCSV\Save-Verbose.txt"  | Export-DbaDiagnosticQuery -NoPlanExport -NoQueryExport -Path $Path_Database_GET

        }
    }
}