Get-Content "C:\temp\Settings.ini" | foreach-object -begin {$h=@{}} -process { $k = [regex]::split($_,'='); if(($k[0].CompareTo("") -ne 0) -and ($k[0].StartsWith("[") -ne $True)) { $h.Add($k[0], $k[1]) } }
$server        = $h.Get_Item("centralServer")
$inventoryDB   = $h.Get_Item("inventoryDB")

if($server.length -eq 0){
    Write-Host "You must provide a value for the 'centralServer' in your Settings.ini file!!!" -BackgroundColor Red
    exit
}
if($inventoryDB.length -eq 0){
    Write-Host "You must provide a value for the 'inventoryDB' in your Settings.ini file!!!" -BackgroundColor Red
    exit
}

$mslExistenceQuery = "
SELECT Count(*) FROM dbo.sysobjects where id = object_id(N'[inventory].[MasterServerList]') and OBJECTPROPERTY(id, N'IsTable') = 1
"
$result = Invoke-Sqlcmd -Query $mslExistenceQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop 

if($result[0] -eq 0){
    Write-Host "The table [inventory].[MasterServerList] wasn't found!!!" -BackgroundColor Red 
    exit
}

$enoughInstancesInMSLQuery = "
SELECT COUNT(*) FROM inventory.MasterServerList WHERE is_active = 1
"
$result = Invoke-Sqlcmd -Query $enoughInstancesInMSLQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop 

if($result[0] -eq 0){
    Write-Host "There are no active instances registered to work with!!!" -BackgroundColor Red 
    exit
}

if ($h.Get_Item("username").length -gt 0 -and $h.Get_Item("password").length -gt 0) {
    $username   = $h.Get_Item("username")
    $password   = $h.Get_Item("password")
}

#Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance,[int]$trusted){
    if($trusted -eq 1){ 
        try{
            Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop
        }
        catch{
            [string]$message = $_
            $errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Monitor-MSSQL-Instance-WhoIsActive','"+$message.replace("'","''")+"',GETDATE())"
            Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
        }
    }
    else{
        try{
            Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop
        }
        catch{
            [string]$message = $_
            $errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Monitor-MSSQL-Instance-WhoIsActive','"+$message.replace("'","''")+"',GETDATE())"
            Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
        }
    }
}

#######################################
#WhoIsActive monitoring table creation#
#######################################
$monitoringTableQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[monitoring].[WhoIsActive]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [monitoring].[WhoIsActive](
    [serverId]                  INT NOT NULL,
    [dd hh:mm:ss.mss]           VARCHAR(8000) NULL,
    [session_id]                SMALLINT NOT NULL,
    [sql_text]                  XML NULL,
    [login_name]                NVARCHAR(128) NOT NULL,
    [wait_info]                 NVARCHAR(4000) NULL,
    [blocking_session_id]       SMALLINT NULL,
    [reads]                     VARCHAR(30) NULL,
    [writes]                    VARCHAR(30) NULL,
    [physical_reads]            VARCHAR(30) NULL,
    [CPU]                       VARCHAR(30) NULL,
    [used_memory]               VARCHAR(30) NULL,
    [status]                    VARCHAR(30) NOT NULL,
    [host_name]                 NVARCHAR(128) NULL,
    [database_name]             NVARCHAR(128) NULL,
    [program_name]              NVARCHAR(128) NULL,
    [start_time]                DATETIME NOT NULL,
    [data_collection_timestamp] DATETIME NOT NULL

    CONSTRAINT FK_WhoIsActive_MasterServerList FOREIGN KEY (serverId) REFERENCES inventory.MasterServerList(serverId) ON DELETE NO ACTION ON UPDATE NO ACTION,

) ON [PRIMARY]
END
"
Execute-Query $monitoringTableQuery $inventoryDB $server 1

#Select the instances from the Master Server List that will be traversed
$instanceLookupQuery = "
SELECT
        serverId,
        trusted,
		CASE instance 
			WHEN 'MSSQLSERVER' THEN server_name                                   
			ELSE CONCAT(server_name,'\',instance)
		END AS 'instance',
		CASE instance 
			WHEN 'MSSQLSERVER' THEN ip                                   
			ELSE CONCAT(ip,'\',instance)
		END AS 'ip',
        CONCAT(ip,',',port) AS 'port'
FROM inventory.MasterServerList
WHERE is_active = 1
"
$instances = Execute-Query $instanceLookupQuery $inventoryDB $server 1

#For each instance, fetch the desired information
$monitoringInformationQuery = "
IF OBJECT_ID('tempdb..#WhoIsActive') IS NULL 
BEGIN
CREATE TABLE #WhoIsActive(
    [dd hh:mm:ss.mss]           VARCHAR(8000) NULL,
    [session_id]                SMALLINT NOT NULL,
    [sql_text]                  XML NULL,
    [login_name]                NVARCHAR(128) NOT NULL,
    [wait_info]                 NVARCHAR(4000) NULL,
    [blocking_session_id]       SMALLINT NULL,
    [reads]                     VARCHAR(30) NULL,
    [writes]                    VARCHAR(30) NULL,
    [physical_reads]            VARCHAR(30) NULL,
    [CPU]                       VARCHAR(30) NULL,
    [used_memory]               VARCHAR(30) NULL,
    [status]                    VARCHAR(30) NOT NULL,
    [host_name]                 NVARCHAR(128) NULL,
    [database_name]             NVARCHAR(128) NULL,
    [program_name]              NVARCHAR(128) NULL,
    [start_time]                DATETIME NOT NULL
)
END
ELSE TRUNCATE TABLE #WhoIsActive

EXEC sp_WhoIsActive @destination_table = '#WhoIsActive', @output_column_list = '[dd hh:mm:ss.mss][session_id][sql_text][login_name][wait_info][blocking_session_id][reads][writes][physical_reads][CPU][used_memory][status][host_name][database_name][program_name][start_time]'

SELECT 
    [dd hh:mm:ss.mss] AS 'query_time',
    [session_id],
    [sql_text],
    [login_name],
    [wait_info],
    [blocking_session_id],
    [reads],
    [writes],
    [physical_reads],
    [CPU] AS 'cpu',
    [used_memory],
    [status],
    [host_name],
    [database_name],
    [program_name],
    [start_time]
FROM #WhoIsActive
"

foreach ($instance in $instances){
   if($instance.trusted -eq 'True'){$trusted = 1}else{$trusted = 0}
   $sqlInstance = $instance.instance

   #Go grab the complementary information for the instance
   Write-Host "Fetching WhoIsActive information from instance" $instance.instance
   
   #Special logic for cases where the instance isn't reachable by name
   try{
        $results = Execute-Query $monitoringInformationQuery "master" $sqlInstance $trusted
   }
   catch{
        $sqlInstance = $instance.ip
        [string]$message = $_
        $query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Monitor-MSSQL-Instance-WhoIsActive','"+$message.replace("'","''")+"',GETDATE())"
        Execute-Query $query $inventoryDB $server 1

        try{  
            $results = Execute-Query $monitoringInformationQuery "master" $sqlInstance $trusted
        }
        catch{
            $sqlInstance = $instance.port
            [string]$message = $_
            $query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Monitor-MSSQL-Instance-WhoIsActive','"+$message.replace("'","''")+"',GETDATE())"
            Execute-Query $query $inventoryDB $server 1

            try{
                $results = Execute-Query $monitoringInformationQuery "master" $sqlInstance $trusted
            }
            catch{
                [string]$message = $_
                $query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Monitor-MSSQL-Instance-WhoIsActive','"+$message.replace("'","''")+"',GETDATE())"
                Execute-Query $query $inventoryDB $server 1
            }
        }
   }
   
   #Perform the INSERT in the monitoring.WhoIsActive only if it returns information
   if($results.Length -ne 0){
      #Build the insert statement
      $insert = "INSERT INTO monitoring.WhoIsActive VALUES"
      foreach($result in $results){   
            if($result['query_time'] -is [DBNull]){$queryTime = "''"}else{$queryTime = $result['query_time']}
            if($result['sql_text'] -is [DBNull]){$sqlText = "''"}else{$sqlText = $result['sql_text']; $sqlText = $sqlText -replace "'","''"}
            if($result['wait_info'] -is [DBNull]){$waitInfo = "''"}else{$waitInfo = $result['wait_info']}
            if($result['blocking_session_id'] -is [DBNull]){$blockingSessionId = "''"}else{$blockingSessionId = $result['blocking_session_id']}
            if($result['reads'] -is [DBNull]){$reads = "''"}else{$reads = $result['reads']}
            if($result['writes'] -is [DBNull]){$writes = "''"}else{$writes = $result['writes']}
            if($result['physical_reads'] -is [DBNull]){$physicalReads = "''"}else{$physicalReads = $result['physical_reads']}
            if($result['cpu'] -is [DBNull]){$cpu = "''"}else{$cpu = $result['cpu']}
            if($result['used_memory'] -is [DBNull]){$usedMemory = "''"}else{$usedMemory = $result['used_memory']}
            if($result['host_name'] -is [DBNull]){$hostName = "''"}else{$hostName = $result['host_name']}
            if($result['database_name'] -is [DBNull]){$databaseName = "''"}else{$databaseName = $result['database_name']}
            if($result['program_name'] -is [DBNull]){$programName = "''"}else{$programName = $result['program_name']}

            $insert += "('$($instance.serverId)',
                         '$($queryTime)',
                          $($result.session_id),
                         '$($sqlText)',
                         '$($result.login_name)',
                         '$($waitInfo)',
                          $($blockingSessionId),
                         '$($reads)',
                         '$($writes)',
                         '$($physicalReads)',
                         '$($cpu)',
                         '$($usedMemory)',
                         '$($result.status)',
                         '$($hostName)',
                         '$($databaseName)',
                         '$($programName)',
                         '$($result.start_time)',
                         GETDATE()
                        ),
                       "      
            }
            $insert = $insert -replace "'''',",'NULL,'
            Execute-Query $insert.Substring(0,$insert.LastIndexOf(',')) $inventoryDB $server 1
   }
}

#Clean the fields that might be used to sort the result set
Execute-Query "UPDATE monitoring.WhoIsActive SET CPU = LTRIM(RTRIM(CPU)), reads = LTRIM(RTRIM(reads)), writes = LTRIM(RTRIM(writes)), physical_reads = LTRIM(RTRIM(physical_reads)), used_memory = LTRIM(RTRIM(used_memory))" $inventoryDB $server 1

Write-Host "Done!"