Last time I went through my script to gather server information for my inventory. Today I’m going to go through my script to collect SQL Server instance data. So, I think I made it clear last time that I’m not a Powershell guru, and there are probably countless ways in which this script can be improved, yada yada.  ‘Nuff said.

Credits

Like the server info script, this script is based heavily on Allen White’s inventory script on Simple-Talk. If you haven’t already seen it, I highly recommend checking out the entire article. I’ve made some changes to Allen’s base script, to suit my own needs. Whereas Allen writes his output to a csv file, I wanted to load directly into my inventory database. Allen also reads in his list of servers to inventory from a file, I keep mine in a master table, also in my inventory database.

Tools you’ll need

In order to load your inventory data directly into SQL Server, you’ll need to download a couple of PowerShell functions.

Name: Write-DataTable.ps1

Author: Chad Miller
http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

Loads data into from a datatable object into SQL Server tables

Name: Out-DataTable.ps1

Author: Chad Miller
http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

Formats input into a datatable object which can then be imported into SQL Server using Write-DataTable.

Permissions required

You’ll need a SQL Server login on each instance you plan to inventory.  The permissions for that login will depend on what data you’re collecting.  You can use the principle of least permissions and only grant the minimum required, which is what I do.  In fact I use certificate-signed procedures to gather any info that requires sysadmin-level rights.  This method requires creating procedures on all target servers, which was a larger footprint than I would have preferred, but I felt it was a better option from a security standpoint. Or you can simply use a sysadmin login and be done with it.  Potayto potahto.

The moment you’ve all been waiting for – The script

Parameters

My script accepts 4 parameters:

• Inventory database instance
• Inventory login
• Inventory password (I use the same login to collect my info and store it)
• Environment (Which environment do I want to inventory?)

A word of explanation about the Environment parameter. As I mentioned earlier, I store my master instance list in a table inside my inventory database. That table holds all instances across all of my environments, Production, Development, etc., and the environment for each instance is also stored in this master table. When I run this script I use this parameter to specify which environment I want to inventory.

Clean out tables pre-run

This script performs a full load every time, so my first step is simply to clear out any existing data.

$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### Clean out all tables pre-run. Move Jobs to Jobs_Compare, that way we can see what jobs were dropped #####
$query = "select instance_name_short into #cmm from Servers where Environment in ('$ssi_env')
      DELETE FROM [SQL].Login_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Users where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Files where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Roles where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Server_Roles where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Credentials where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Linked_Server_Logins where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Linked_Servers where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Schedules where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Schedule_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Info_Compare where instance_name in (select instance_name_short from #cmm);
      INSERT INTO [SQL].Job_Info_Compare SELECT * FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Proxies where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Proxies where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Backup_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Instance_Info where instance_name in (select instance_name_short from #cmm);
      "
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()

Get list of servers to inventory

The next step in the process is to read the master table to get the list of servers to inventory. Then we’ll step through them and for each one we’ll check and make sure it’s online and if it is, we’ll execute the get-sqlinfo function.

##### Get the list of servers to inventory #####
$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort, instance_name as InstanceName, instance_name_short as InstanceNameShort FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
##### For every server gather data #####
while($reader.Read()) {
    ##### See if the server is alive #####
    $svr = $reader['ServerName']
    $svrshort = $reader['ServerNameShort']
    $inst = $reader['InstanceName']
    $instshort = $reader['InstanceNameShort']
    $result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst, $ssi_userid, $ssi_passwd)
       $responds = $false
    if ($result.isopen -eq 0) {
        $responds = $true
    }
    ##### If it is alive ... #####
    If ($responds) {
        #Write-Output "$inst is alive"
        get-sqlinfo $svr $svrshort $inst $instshort
    }
    else {
              # Let the user know we couldn't connect to the server
              Write-Output "$svr does not respond"
       }
}

Get-sqlinfo Function

The last part of the script is to actually gather the information.

First up on the list: Instance info.  As you’ll soon see, most of my script uses the same basic format, over and over.  The instance info section is the exception.  Here I’m making use of the Server SMO object class to get additional instance-level information, like the version, edition, collation, whether it’s clustered, and database/log paths.  I could have used T-SQL to get all of this info, like the rest of the script, but this way seemed easier and it let me add another tool to the arsenal.  I then combine this information with some additional fields I’ve obtained using T-SQL.  To load the data into our Inventory database, we use the same Write-DataTable and Out-Datatable functions we used in the server inventory script.

# Create an ADO.Net connection to the instance
    $ServerConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $ServerConn.ServerInstance="$instance"
    $ServerConn.LoginSecure = $false
    $ServerConn.Login = $ssi_userid
    $ServerConn.Password = $ssi_passwd
    $ServerConn.Connect()
    $s = new-object Microsoft.SqlServer.Management.SMO.Server($ServerConn)
    ##### Get SQL Instance Info #####
    $ssi_table="SQL.Instance_Info"
    $connection2 = new-object system.data.sqlclient.sqlconnection( `
            "Data Source=$instance;Initial Catalog=master;User Id=$ssi_userid; Password=$ssi_passwd;");
    $connection2.Open()
    $cmd2 = $connection2.CreateCommand()
    $query = "DECLARE @ServiceAccount varchar(100)
            EXECUTE master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
            N'ObjectName',
            @ServiceAccount OUTPUT,
            N'no_output'
            SELECT @ServiceAccount as svc_acct
            ,(select value from sys.configurations where name = 'cost threshold for parallelism') as parallel_threshold
            ,(select value from sys.configurations where name = 'max degree of parallelism') as max_dop
            ,(select value from sys.configurations where name = 'min server memory (MB)') as min_memory
            ,(select value from sys.configurations where name = 'max server memory (MB)') as max_memory
            ,(select value from sys.configurations where name = 'xp_cmdshell') as xp_cmdshell_enabled"
    $cmd2.CommandText = $query
    $reader2 = $cmd2.ExecuteReader()
    if ($reader2.Read() )
    {
        $svcacct = $reader2['svc_acct']
        $parallel_threshold = $reader2['parallel_threshold']
        $max_dop = $reader2['max_dop']
        $min_memory = $reader2['min_memory']
        $max_memory = $reader2['max_memory']
        $xp_cmdshell_enabled = $reader2['xp_cmdshell_enabled']
    }
    $dt= $s.Information | SELECT @{n="InstanceName";e={$instanceshort}}, VersionString, ProductLevel, EngineEdition, Collation, IsClustered, MasterDBPath, MasterDBLogPath, RootDirectory, @{n="ServiceAccount";e={$svcacct}},@{n="parallel_threshold";e={$parallel_threshold}}, @{n="max_dop";e={$max_dop}}, @{n="min_memory";e={$min_memory}}, @{n="max_memory";e={$max_memory}}, @{n="xp_cmdshell_enabled";e={$xp_cmdshell_enabled}} | Out-DataTable
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

For login information, I’m simply defining a connection string and a query string that gets me the login data I want.  I create a SQLDataAdapter object ($da), use it to fill a DataTable object ($dt), and load it up into my Inventory table.

##### Get Login Info #####
    $conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"
    $ssi_table="SQL.Login_Info"
    $query = "SELECT @@SERVERNAME as instance_name, sp.[name] as LoginName, sp.create_date AS AccountCreateDate,
                sp.modify_date AS LastTimeAccountModified,
                LOGINPROPERTY( sp.[name], 'PasswordLastSetTime' ) AS PasswordLastSetTime,
                sp.default_database_name as DefaultDatabase,
                sp.default_language_name as DefaultLanguage,
                sl.is_policy_checked as IsPolicyChecked,
                sl.is_expiration_checked as IsExpirationChecked,
            LOGINPROPERTY( sp.[name], 'IsExpired' ) AS IsExpired,
            LOGINPROPERTY (sp.[name], 'DaysUntilExpiration' ) AS DaysUntilExpiration,
            LOGINPROPERTY( sp.[name], 'IsLocked' ) AS IsLocked,
                LOGINPROPERTY( sp.[name], 'IsMustChange' ) AS IsMustChange,
               LOGINPROPERTY( sp.[name], 'LockoutTime' ) AS LockoutTime,
                LOGINPROPERTY( sp.[name], 'BadPasswordCount' ) AS BadPasswordCount,
                LOGINPROPERTY( sp.[name], 'BadPasswordTime' ) AS BadPasswordTime,
                LOGINPROPERTY( sp.[name], 'HistoryLength' ) AS HistoryLength,
                sp.sid, sp.type
        FROM sys.server_principals sp
        LEFT OUTER JOIN sys.sql_logins sl on sl.sid = sp.sid
        WHERE sp.type not in ('C', 'R')"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

For the remainder of the tables, I use the same basic format.  The $ssi_table variable specifies my target table, and the $query variable holds the T-SQL query to execute.  The rest is all the same, over and over.  Could I have simplified it more by creating a function to handle the loading process and simply passed in the $ssi_table and $query?  Absolutely.  In fact, I’ll leave that for you to do.  Call it homework.    (And this is why I will probably *not* be the next Ola.)  Here’s an example.

##### Get Credential Info #####
    $ssi_table="SQL.Credentials"
    $query = "select @@servername as instance_name, credential_id, name, credential_identity, create_date, modify_date
            from msdb.sys.credentials"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

One last thing

The last thing this script is execute a stored procedure in my inventory database called usp_StoreDBGrowth.  All this procedure does is archive the newly inserted datafile information into a separate table for historical growth analysis.

The complete script

So here’s the complete script. Give it a whirl and let me know what you think. If you come up with some improvements, I’d love to hear them, too.

# get-SQLInfo.ps1
# usage: ./get-SQLInfo.ps1
# Collects all Inventory information into MyInventory database
# Including:
#    [SQL].Instance_Info;
#    [SQL].Database_Info;
#    [SQL].Login_Info;
#    [SQL].Database_Users;
#    [SQL].Database_Files;
#    [SQL].Server_Roles;
#    [SQL].Database_Roles;
#    [SQL].Linked_Servers;
#    [SQL].Linked_Server_Logins;
param(
    [string]$ssi_instance=$null,
    [string]$ssi_userid=$null,
    [string]$ssi_passwd=$null,
    [string]$ssi_env=$null
     )
. .\write-datatable.ps1
. .\out-datatable.ps1
$ssi_database = "MyInventory"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null
function get-sqlinfo ($server, $servershort, $instance, $instanceshort) {
    # Create an ADO.Net connection to the instance
    $ServerConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $ServerConn.ServerInstance="$instance"
    $ServerConn.LoginSecure = $false
    $ServerConn.Login = $ssi_userid
    $ServerConn.Password = $ssi_passwd
    $ServerConn.Connect()
    $s = new-object Microsoft.SqlServer.Management.SMO.Server($ServerConn)
    ##### Get SQL Instance Info #####
    $ssi_table="SQL.Instance_Info"
    $connection2 = new-object system.data.sqlclient.sqlconnection( `
            "Data Source=$instance;Initial Catalog=master;User Id=$ssi_userid; Password=$ssi_passwd;");
    $connection2.Open()
    $cmd2 = $connection2.CreateCommand()
    $query = "DECLARE @ServiceAccount varchar(100)
            EXECUTE master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
            N'ObjectName',
            @ServiceAccount OUTPUT,
            N'no_output'
            SELECT @ServiceAccount as svc_acct
            ,(select value from sys.configurations where name = 'cost threshold for parallelism') as parallel_threshold
            ,(select value from sys.configurations where name = 'max degree of parallelism') as max_dop
            ,(select value from sys.configurations where name = 'min server memory (MB)') as min_memory
            ,(select value from sys.configurations where name = 'max server memory (MB)') as max_memory
            ,(select value from sys.configurations where name = 'xp_cmdshell') as xp_cmdshell_enabled"
    $cmd2.CommandText = $query
    $reader2 = $cmd2.ExecuteReader()
    if ($reader2.Read() )
    {
        $svcacct = $reader2['svc_acct']
        $parallel_threshold = $reader2['parallel_threshold']
        $max_dop = $reader2['max_dop']
        $min_memory = $reader2['min_memory']
        $max_memory = $reader2['max_memory']
        $xp_cmdshell_enabled = $reader2['xp_cmdshell_enabled']
    }
    $dt= $s.Information | SELECT @{n="InstanceName";e={$instanceshort}}, VersionString, ProductLevel, EngineEdition, Collation, IsClustered, MasterDBPath, MasterDBLogPath, RootDirectory, @{n="ServiceAccount";e={$svcacct}},@{n="parallel_threshold";e={$parallel_threshold}}, @{n="max_dop";e={$max_dop}}, @{n="min_memory";e={$min_memory}}, @{n="max_memory";e={$max_memory}}, @{n="xp_cmdshell_enabled";e={$xp_cmdshell_enabled}} | Out-DataTable
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Get Login Info #####
    $conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"
    $ssi_table="SQL.Login_Info"
    $query = "SELECT @@SERVERNAME as instance_name, sp.[name] as LoginName, sp.create_date AS AccountCreateDate,
                sp.modify_date AS LastTimeAccountModified,
                LOGINPROPERTY( sp.[name], 'PasswordLastSetTime' ) AS PasswordLastSetTime,
                sp.default_database_name as DefaultDatabase,
                sp.default_language_name as DefaultLanguage,
                sl.is_policy_checked as IsPolicyChecked,
                sl.is_expiration_checked as IsExpirationChecked,
            LOGINPROPERTY( sp.[name], 'IsExpired' ) AS IsExpired,
            LOGINPROPERTY (sp.[name], 'DaysUntilExpiration' ) AS DaysUntilExpiration,
            LOGINPROPERTY( sp.[name], 'IsLocked' ) AS IsLocked,
                LOGINPROPERTY( sp.[name], 'IsMustChange' ) AS IsMustChange,
               LOGINPROPERTY( sp.[name], 'LockoutTime' ) AS LockoutTime,
                LOGINPROPERTY( sp.[name], 'BadPasswordCount' ) AS BadPasswordCount,
                LOGINPROPERTY( sp.[name], 'BadPasswordTime' ) AS BadPasswordTime,
                LOGINPROPERTY( sp.[name], 'HistoryLength' ) AS HistoryLength,
                sp.sid, sp.type
        FROM sys.server_principals sp
        LEFT OUTER JOIN sys.sql_logins sl on sl.sid = sp.sid
        WHERE sp.type not in ('C', 'R')"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Get Credential Info #####
    $ssi_table="SQL.Credentials"
    $query = "select @@servername as instance_name, credential_id, name, credential_identity, create_date, modify_date
            from msdb.sys.credentials"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Jobs #####
    $ssi_table = "SQL.Job_Info"
    $query = ";with laststatus as (
            select job_id, start_execution_date, stop_execution_date from
            (select job_id, start_execution_date, stop_execution_date, dense_rank() over (partition by job_id order by start_execution_date desc) as rownum
            from msdb..sysjobactivity where start_execution_date is not null) a
                where a.rownum = 1)
          , maxinstance as (
               select max(instance_id) maxinst_id, job_id from msdb.dbo.sysjobhistory
                        group by job_id)
          , laststep as (
            select j.job_id, max(j.instance_id) as laststep_id
                from msdb.dbo.sysjobhistory j
                join maxinstance mi on j.job_id = mi.job_id
                    and j.instance_id < mi.maxinst_id                  join laststatus ls on ls.job_id = j.job_id                      and CAST( CONVERT(VARCHAR, j.run_date) + ' ' + STUFF(STUFF(RIGHT('000000'                       + CONVERT(VARCHAR,j.run_time),6),5,0,':'),3,0,':') AS DATETIME )     >= ls.start_execution_date
            where run_status = 0
            group by j.job_id)
          select @@SERVERNAME as instance_name, j.name as job_name, j.enabled, j.description, j.date_created, j.date_modified
             , l.start_execution_date as last_run_date
             , isnull(case
                when l.stop_execution_date is null then 'In-Progress'
                else case jh.run_status
                        when 0 then 'Failed '
                        when 1 then 'Succeeded '
                        when 2 then 'Retry'
                        when 3 then 'Canceled '
                        when 4 then 'In-progress'
                        when 5 then 'Unknown ' END END , 'Unknown') as 'last_run_status'
             , jhm.message, j.job_id
            FROM msdb.dbo.sysjobs j
                left outer join laststatus l
                    on j.job_id = l.job_id
                left outer join msdb.dbo.sysjobhistory jh
                join maxinstance jh2
                ON jh2.maxinst_id = jh.instance_id
                    and jh2.job_id = jh.job_id
                ON j.job_id = jh.job_id
                LEFT JOIN laststep ls
                JOIN msdb.dbo.sysjobhistory jhm
                    on ls.laststep_id = jhm.instance_id
                    on ls.job_id = jh.job_id
            WHERE j.enabled = 1 and l.start_execution_date is not null"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Get Schedule Info #####
    $ssi_table="SQL.Schedule_Info"
    $query = "select @@SERVERNAME as instance_name, s.schedule_id, s.enabled, s.freq_type, s.freq_interval,
            s.freq_subday_type, s.freq_subday_interval, s.freq_relative_interval, s.freq_recurrence_factor, s.active_start_time, s.active_end_time
            from msdb.dbo.sysschedules s"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Get Job Schedule Info #####
    $ssi_table="SQL.Job_Schedules"
    $query = "select @@SERVERNAME as instance_name, js.job_id, js.schedule_id
            from msdb.dbo.sysjobschedules js"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Get Proxy Info #####
    $ssi_table="SQL.Proxies"
    $query = "select @@servername as instance_name, proxy_id, name, credential_id, enabled, description, user_sid, credential_date_created
            from msdb.dbo.sysproxies"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Get Job Step Proxy Info #####
    $ssi_table="SQL.Job_Proxies"
    $query = "select @@servername as instance_name, j.job_id, j.name as job_name, s.step_name, s.proxy_id
            from msdb.dbo.sysjobs j
            join msdb.dbo.sysjobsteps s on j.job_id = s.job_id and s.proxy_id is not null"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Server Roles #####
    $ssi_table = "SQL.Server_Roles"
    $query = "select @@Servername as instance_name, r.name as role_name, m.name as member_name, m.sid
        from sys.server_principals r
        join sys.server_role_members rm on r.principal_id = rm.role_principal_id
        join sys.server_principals m on m.principal_id = rm.member_principal_id"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Linked Servers #####
    $ssi_table = "SQL.Linked_Servers"
    $query = "declare
         @alias varchar(40)
        ,@regbase nvarchar(30)
        ,@regkey nvarchar(100)
        ,@dirbasepath nvarchar(250)
            ,@dirbasepath2 nvarchar(250)
        SET @regbase = 'HKEY_LOCAL_MACHINE'
        SET @regkey = 'Software\Microsoft\MSSQLServer\Client\ConnectTo\'
        create table #aliases (
        alias varchar(40),
        resolved nvarchar(250))
        declare a1 cursor for select data_source from sys.servers
        open a1
        fetch a1 into @alias
        while @@FETCH_STATUS = 0
        begin
        set @dirbasepath = NULL
        EXECUTE master..xp_regread @regbase
            , @regkey
            , @alias
            , @dirbasepath OUTPUT
            insert into #aliases
            SELECT @alias, substring(@dirbasepath,CHARINDEX(',',@dirbasepath)+1,LEN(@dirbasepath))
        fetch a1 into @alias
        end
        close a1
        deallocate a1
        select distinct @@servername as [Instance], s.name as [Linked_Server], ISNULL(tmp.resolved, s.data_source) as [Remote_Instance], s.provider as [Provider], s.catalog as [Default_Database]
            from master.sys.servers s
            LEFT OUTER JOIN #aliases tmp ON tmp.alias = s.name
            where is_data_access_enabled = 1"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Linked Server Logins #####
    $ssi_table = "SQL.Linked_Server_Logins"
    $query = "SELECT @@servername as [instance_name]
        , s.name as [linked_server]
        , CASE l.uses_self_credential
            WHEN 1 THEN 'UNMAPPED LOGINS'
            ELSE ISNULL(p.name, 'UNMAPPED LOGINS')
            END AS [local_login]
        , CASE l.uses_self_credential
            WHEN 1 THEN 'USE SELF'
            ELSE l.remote_name
            END AS [remote_login]
        FROM sys.linked_logins l
            JOIN sys.servers s ON s.server_id = l.server_id AND is_data_access_enabled = 1
            LEFT OUTER JOIN sys.server_principals p ON p.principal_id = l.local_principal_id
        ORDER BY [linked_server], [local_login]"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Get SQL Database Info #####
    $ssi_table="SQL.Database_Info"
    $query = "select @@SERVERNAME as [instance_name], d.database_id, d.name as [database_name], d.state, d.recovery_model, d.compatibility_level, d.collation_name,
            b.bkup_dt as last_backup_date,
            b.diff_dt as last_differential_backup_date,
            b.log_dt as last_log_backup_date,
            d.create_date,
            --data space usage, index space usage
            isnull(dm.mirroring_role, 0) as is_mirroring_enabled,
            p.name as [owner]
            --primary file path, backup dir
        from master.sys.databases d
        join master.sys.database_mirroring dm on d.database_id = dm.database_id
        join master.sys.server_principals p on d.owner_sid = p.sid
        left join
            (SELECT bs.database_name,
            MAX(case bs.type when 'D' then bs.backup_start_date end) as bkup_dt,
            MAX(case bs.type when 'I' then bs.backup_start_date end) as diff_dt,
            MAX(case bs.type when 'L' then bs.backup_start_date end) as log_dt
            FROM msdb.dbo.backupset bs
            WHERE bs.server_name = @@Servername
            and bs.is_copy_only = 0
            group by bs.database_name) b on b.database_name = d.name"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Get Database User Info #####
    $ssi_table="SQL.Database_Users"
    $query = "exec sp_get_database_users"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Database Roles #####
    $ssi_table = "SQL.Database_Roles"
    $query = "exec sp_get_database_roles"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Database    Files #####
    $ssi_table = "SQL.Database_Files"
    $query = "select @@SERVERNAME as instance_name, DB_Name(database_id) as database_name, file_id, type_desc, name, physical_name, (size)*8/1024 as size_mb
        ,case (is_percent_growth) WHEN 1 THEN growth ELSE 0 END  as growth_percent
        ,case (is_percent_growth) WHEN 0 THEN growth*8/1024 ELSE 0 END  as growth_mb
        from sys.master_files
        WHERE type in (0, 1)"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### Backup Info #####
    $ssi_table = "SQL.Backup_Info"
    $query = "DECLARE @db_name sysname ,@restore_to_datetime  datetime , @server_name nvarchar(512);
select @restore_to_datetime = GETDATE();
set @server_name = cast(serverproperty(N'Servername') as nvarchar(512));
        CREATE TABLE #backupset(
            backup_set_id            INTEGER    NOT NULL,
            is_in_restore_plan        BIT NOT NULL,
            backup_start_date        DATETIME NOT NULL,
            type                CHAR(1)    NOT NULL,
            database_name            NVARCHAR(256) NOT NULL,
            database_guid            UNIQUEIDENTIFIER,
            family_guid            UNIQUEIDENTIFIER,
            first_recovery_fork_guid    UNIQUEIDENTIFIER,
            last_recovery_fork_guid        UNIQUEIDENTIFIER,
            first_lsn            NUMERIC(25, 0),
            last_lsn            NUMERIC(25, 0),
            checkpoint_lsn            NUMERIC(25, 0),
            database_backup_lsn        NUMERIC(25, 0),
            fork_point_lsn            NUMERIC(25, 0),
            restore_till_lsn        NUMERIC(25, 0),
            backup_set_uuid            UNIQUEIDENTIFIER,
            differential_base_guid        UNIQUEIDENTIFIER
        );
         SELECT backupset_outer.database_name
            ,    backupset_outer.backup_set_id
              ,backupset_outer.backup_start_date
          into #most_recent
          FROM msdb.dbo.backupset backupset_outer
             WHERE  backupset_outer.server_name = @server_name
           AND backupset_outer.type <> 'L' -- Full and differential Database Backups
           AND backupset_outer.backup_start_date = (  SELECT MAX(backupset_inner.backup_start_date)
                                                    FROM msdb.dbo.backupset backupset_inner
                                                   WHERE backupset_inner.database_name = backupset_outer.database_name
                             AND backupset_inner.server_name = @server_name
                                                     AND backupset_inner.type = backupset_outer.type
                                                     AND backupset_inner.backup_start_date                                                      AND backupset_inner.is_copy_only = 0 )
           AND backupset_outer.is_copy_only = 0;
        INSERT #backupset(
           backup_set_id
          ,is_in_restore_plan
          ,backup_start_date
          ,type
          ,database_name
          ,last_recovery_fork_guid
        )
        SELECT msdb.dbo.backupset.backup_set_id
              ,1                   --  The full database backup is always needed for the restore plan
              ,msdb.dbo.backupset.backup_start_date
              ,msdb.dbo.backupset.type
              ,msdb.dbo.backupset.database_name
              ,msdb.dbo.backupset.last_recovery_fork_guid
          FROM msdb.dbo.backupset
          JOIN #most_recent m
         on msdb.dbo.backupset.backup_set_id = m.backup_set_id
         AND msdb.dbo.backupset.server_name = @server_name;
        select @server_name as instance_name, btmp.database_name, bkmf.media_set_id, bkmf.family_sequence_number, bkmf.physical_device_name
            ,  bkps.backup_start_date, bkps.backup_finish_date, bkps.first_family_number, bkps.last_family_number, bkps.type as backup_type
        from
        #backupset AS btmp
INNER JOIN msdb.dbo.backupset AS bkps ON bkps.backup_set_id = btmp.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bkmf ON bkps.media_set_id = bkmf.media_set_id;
        drop table #backupset;
        drop table #most_recent;"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
} #get-sqlinfo
$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### Clean out all tables pre-run. Move Jobs to Jobs_Compare, that way we can see what jobs were dropped #####
$query = "select instance_name_short into #cmm from Servers where Environment in ('$ssi_env')
      DELETE FROM [SQL].Login_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Users where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Files where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Roles where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Server_Roles where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Credentials where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Linked_Server_Logins where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Linked_Servers where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Schedules where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Schedule_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Info_Compare where instance_name in (select instance_name_short from #cmm);
      INSERT INTO [SQL].Job_Info_Compare SELECT * FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Proxies where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Proxies where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Backup_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Instance_Info where instance_name in (select instance_name_short from #cmm);
      "
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()
##### Get the list of servers to inventory #####
$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort, instance_name as InstanceName, instance_name_short as InstanceNameShort FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
##### For every server gather data #####
while($reader.Read()) {
    ##### See if the server is alive #####
    $svr = $reader['ServerName']
    $svrshort = $reader['ServerNameShort']
    $inst = $reader['InstanceName']
    $instshort = $reader['InstanceNameShort']
    $result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst, $ssi_userid, $ssi_passwd)
       $responds = $false
    if ($result.isopen -eq 0) {
        $responds = $true
    }
    ##### If it is alive ... #####
    If ($responds) {
        #Write-Output "$inst is alive"
        get-sqlinfo $svr $svrshort $inst $instshort
    }
    else {
              # Let the user know we couldn't connect to the server
              Write-Output "$svr does not respond"
       }
}
##### Execute usp_StoreDBGrowth procedure #####
$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
$query = "exec [SQL].usp_StoreDBGrowth"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()

Edit: Here’s the script to create the SQL tables.

 USE [MyInventory]
GO
CREATE TABLE [SQL].[Job_Schedules](
[instance_name] [nvarchar](128) NULL,
[job_id] [uniqueidentifier] NULL,
[schedule_id] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Schedule_Info](
[instance_name] [nvarchar](128) NULL,
[schedule_id] [int] NOT NULL,
[enabled] [int] NOT NULL,
[freq_type] [int] NOT NULL,
[freq_interval] [int] NOT NULL,
[freq_subday_type] [int] NOT NULL,
[freq_subday_interval] [int] NOT NULL,
[freq_relative_interval] [int] NOT NULL,
[freq_recurrence_factor] [int] NOT NULL,
[active_start_time] [int] NOT NULL,
[active_end_time] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Backup_Info](
[instance_name] [nvarchar](128) NOT NULL,
[database_name] [nvarchar](128) NOT NULL,
[media_set_id] [int] NOT NULL,
[family_sequence_number] [tinyint] NOT NULL,
[physical_device_name] [nvarchar](260) NULL,
[backup_start_date] [datetime] NULL,
[backup_finish_date] [datetime] NULL,
[first_family_number] [tinyint] NULL,
[last_family_number] [tinyint] NULL,
[backup_type] [char](1) NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Database_Info](
[instance_name] [nvarchar](128) NOT NULL,
[database_id] [smallint] NULL,
[database_name] [nvarchar](128) NOT NULL,
[status] [varchar](40) NULL,
[RecoveryModel] [varchar](20) NULL,
[CompatibilityLevel] [varchar](20) NULL,
[Collation] [varchar](50) NULL,
[LastBackupDate] [datetime] NULL,
[LastDifferentialBackupDate] [datetime] NULL,
[LastLogBackupDate] [datetime] NULL,
[CreateDate] [datetime] NULL,
[IsMirroringEnabled] [varchar](5) NULL,
[Owner] [varchar](50) NULL,
 CONSTRAINT [PK_Instance_Database] PRIMARY KEY CLUSTERED
(
[instance_name] ASC,
[database_name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Instance_Info](
[instance_name] [nvarchar](128) NOT NULL,
[Version] [varchar](20) NULL,
[SPLevel] [varchar](10) NULL,
[Edition] [varchar](50) NULL,
[Collation] [varchar](50) NULL,
[IsClustered] [varchar](5) NULL,
[MasterDBPath] [varchar](200) NULL,
[MasterDBLogPath] [varchar](200) NULL,
[RootDirectory] [varchar](200) NULL,
[ServiceAccount] [varchar](50) NULL,
[Parallel_Threshold] [int] NULL,
[Max_DOP] [int] NULL,
[Min_Memory] [int] NULL,
[Max_Memory] [int] NULL,
[XP_Cmdshell_Enabled] [tinyint] NULL,
 CONSTRAINT [PK_Instance_Name] PRIMARY KEY CLUSTERED
(
[instance_name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Linked_Server_Logins](
[instance_name] [nvarchar](128) NOT NULL,
[linked_server] [sysname] NOT NULL,
[local_login] [nvarchar](128) NOT NULL,
[remote_login] [nvarchar](128) NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Linked_Servers](
[Instance_name] [nvarchar](128) NOT NULL,
[Linked_Server] [sysname] NOT NULL,
[Remote_Instance] [nvarchar](4000) NULL,
[Provider] [sysname] NOT NULL,
[Default_Database] [sysname] NULL,
 CONSTRAINT [PK_instance_linked_server] PRIMARY KEY CLUSTERED
(
[Instance_name] ASC,
[Linked_Server] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [SQL].[Database_Growth](
[instance_name] [nvarchar](128) NULL,
[database_name] [nvarchar](128) NULL,
[datafile_mb] [int] NULL,
[logfile_mb] [int] NULL,
[run_date] [date] NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Job_Proxies](
[instance_name] [nvarchar](128) NOT NULL,
[job_id] [uniqueidentifier] NOT NULL,
[job_name] [sysname] NOT NULL,
[step_name] [sysname] NOT NULL,
[proxy_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Proxies](
[instance_name] [nvarchar](128) NOT NULL,
[proxy_id] [int] NOT NULL,
[name] [sysname] NOT NULL,
[credential_id] [int] NOT NULL,
[enabled] [tinyint] NOT NULL,
[description] [nvarchar](512) NULL,
[user_sid] [varbinary](85) NOT NULL,
[credential_date_created] [datetime] NOT NULL,
 CONSTRAINT [PK_Instance_Proxy] PRIMARY KEY CLUSTERED
(
[instance_name] ASC,
[proxy_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Credentials](
[instance_name] [nvarchar](128) NOT NULL,
[credential_id] [int] NOT NULL,
[name] [sysname] NOT NULL,
[credential_identity] [nvarchar](4000) NULL,
[create_date] [datetime] NOT NULL,
[modify_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Database_Roles](
[instance_name] [nvarchar](128) NOT NULL,
[database_name] [nvarchar](128) NOT NULL,
[role_name] [varchar](100) NULL,
[database_user] [varchar](200) NULL,
[dbuser_sid] [varbinary](85) NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Job_Info_Compare](
[instance_name] [nvarchar](128) NULL,
[job_name] [sysname] NOT NULL,
[enabled] [tinyint] NOT NULL,
[description] [nvarchar](512) NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NOT NULL,
[last_run_date] [datetime] NULL,
[last_run_status] [varchar](20) NULL,
[message] [nvarchar](4000) NULL,
[job_id] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Database_Users](
[instance_name] [nvarchar](128) NOT NULL,
[database_name] [nvarchar](128) NOT NULL,
[database_user] [varchar](100) NULL,
[create_date] [datetime] NULL,
[dbuser_sid] [varbinary](max) NULL,
[dbuser_type] [char](1) NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Login_Info](
[instance_name] [nvarchar](128) NOT NULL,
[LoginName] [sysname] NOT NULL,
[AccountCreateDate] [datetime] NOT NULL,
[LastTimeAccountModified] [datetime] NOT NULL,
[PasswordLastSetTime] [sql_variant] NULL,
[DefaultDatabase] [sysname] NULL,
[DefaultLanguage] [sysname] NULL,
[IsPolicyChecked] [bit] NULL,
[IsExpirationChecked] [bit] NULL,
[IsExpired] [sql_variant] NULL,
[DaysUntilExpiration] [sql_variant] NULL,
[IsLocked] [sql_variant] NULL,
[IsMustChange] [sql_variant] NULL,
[LockoutTime] [sql_variant] NULL,
[BadPasswordCount] [sql_variant] NULL,
[BadPasswordTime] [sql_variant] NULL,
[HistoryLength] [sql_variant] NULL,
[LoginSid] [varbinary](85) NULL,
[LoginType] [char](1) NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Server_Roles](
[instance_name] [nvarchar](128) NOT NULL,
[RoleName] [nvarchar](128) NULL,
[LoginName] [nvarchar](128) NULL,
[LoginSid] [varbinary](85) NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Database_Files](
[instance_name] [nvarchar](128) NOT NULL,
[database_name] [nvarchar](128) NOT NULL,
[file_id] [int] NOT NULL,
[type_desc] [nvarchar](60) NULL,
[name] [sysname] NOT NULL,
[physical_name] [nvarchar](260) NOT NULL,
[size_mb] [int] NULL,
[growth_percent] [int] NOT NULL,
[growth_mb] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [SQL].[Job_Info](
[instance_name] [nvarchar](128) NOT NULL,
[job_name] [sysname] NOT NULL,
[enabled] [tinyint] NOT NULL,
[description] [nvarchar](512) NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NOT NULL,
[last_run_date] [datetime] NULL,
[last_run_status] [varchar](20) NULL,
[message] [nvarchar](4000) NULL,
[job_id] [uniqueidentifier] NULL
) ON [PRIMARY]
GO