December 22, 2019 at 1:23 am
I am trying to use this powershell script to pull SQL Server information and place it in a table. It works great if I pass the server names in a comma delimited list. But I need it to work by pulling the list from the CMS(Central Management Server). The output from that query is a list of servers (1 per line). It works for other scripts but not this one.
Here is the whole script but it is the last few lines I am having a problem with.
# Assume you have SQLServer PowerShell module installed
# on the server where you execute this script
Import-Module sqlserver -DisableNameChecking;
function Get-SQLDBInventory
{
[cmdletbinding()]
Param( [Parameter(Mandatory=$false, ValueFromPipeline=$true)]
[Alias("SQLServer","Instance")]
[string[]]$ServerInstance = $env:computername
)
[string]$qry = @"
set nocount on;
if object_id('tempdb..#t', 'U') is not null
drop table #t;
create table #t (
ServerName varchar(128) default @@servername
, DBName varchar(128) default db_name()
, DBOwner varchar(128)
, CreateDate datetime2
, RecoveryModel varchar(12)
, StateDesc varchar(60)
, CompatibilityLevel int
, DataFileSizeMB int
, LogFileSizeMB int
, DataUsageMB int
, IndexUsageMB int
, SizeMB decimal(17,2)
, Collation varchar(60)
, UserCount int
, RoleCount int
, TableCount int
, SPCount int
, UDFCount int
, ViewCount int
, DMLTriggerCount int
, IsCaseSensitive bit
, IsTrustWorthy bit
, LastFullBackupDate datetime2
, LastDiffBackupDate datetime2
, LastLogBackupDate datetime2);
insert into #t (DBName, DBOwner, CreateDate, RecoveryModel, StateDesc, CompatibilityLevel, IsCaseSensitive
, IsTrustWorthy, Collation, LastFullBackupDate, LastDiffBackupDate, LastLogBackupDate)
select name, suser_sname(owner_sid), create_date, recovery_model_desc, state_desc,compatibility_level
, IsCaseSensitive=CAST(CHARINDEX(N'_CS_', collation_name) AS bit), is_trustworthy_on, Collation_Name
, t.LastFullBackup, t.LastDiffBackup, t.LastLogBackup
from master.sys.databases db
outer apply ( SELECT
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
FROM msdb.dbo.backupset b
where b.database_name = db.name
) t;
EXEC master.dbo.sp_msforeachdb 'use [?]
update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)
, DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB
, DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize
, TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC
, DMLTriggerCount=y.DC
, UserCount = z.UC, RoleCount = z.RC
from #t t
outer apply (
SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128
, SUM(case when df.type in (1,3) then df.size else 0 end)/128
FROM sys.database_files df
) u(DBSize, LogSize)
outer apply(select DataUsageMB=sum(
CASE
When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
When a.type <> 1 and p.index_id < 2 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END)/128,
IndexUsageMB=(sum(a.used_pages)-sum(
CASE
When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
When a.type <> 1 and p.index_id < 2 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
))/128
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
) x
outer apply
( select SC=Sum(case Type when ''P'' then 1 else 0 end)
, DC=Sum(case Type when ''TR'' then 1 else 0 end)
, TC=Sum(case Type when ''U'' then 1 end)
, UC= sum(case when Type in (''TF'', ''IF'', ''FN'') then 1 else 0 end)
, VC=Sum(case Type when ''V'' then 1 else 0 end)
from sys.objects where object_id > 1024
and type in (''U'',''P'',''TR'',''V'',''TF'',''IF'',''FN'')
) y
outer apply
( select UC = sum(case when [Type] in (''G'',''S'',''U'') then 1 else 0 end)
, RC = sum(case when Type = ''R'' then 1 else 0 end)
from sys.database_principals
where principal_id > 4
) z where t.DBName=db_name();
'
SELECT * FROM #T
"@
$dt2 = new-object System.Data.DataTable;
$dt2.columns.add((new-object System.Data.DataColumn('ServerName' , [System.String])));
$dt2.columns.add((new-object System.Data.DataColumn('DBName' , [System.String])));
$dt2.columns.add((new-object System.Data.DataColumn('DBOwner' , [System.String])));
$dt2.columns.add((new-object System.Data.DataColumn('CreateDate' , [System.DateTime])));
$dt2.columns.add((new-object System.Data.DataColumn('RecoveryModel' , [System.String])));
$dt2.columns.add((new-object System.Data.DataColumn('StateDesc' , [System.String])));
$dt2.columns.add((new-object System.Data.DataColumn('CompatibilityLevel' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('DataFileSizeMB' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('LogFileSizeMB' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('DataUsageMB' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('IndexUsageMB' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('SizeMB' , [System.Decimal])));
$dt2.columns.add((new-object System.Data.DataColumn('Collation' , [System.String])));
$dt2.columns.add((new-object System.Data.DataColumn('UserCount' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('RoleCount' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('TableCount' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('SPCount' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('UDFCount' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('ViewCount' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('DMLTriggerCount' , [System.Int32])));
$dt2.columns.add((new-object System.Data.DataColumn('IsCaseSensitive' , [System.Boolean])));
$dt2.columns.add((new-object System.Data.DataColumn('IsTrustWorthy' , [System.Boolean])));
$dt2.columns.add((new-object System.Data.DataColumn('LastFullBackupDate', [System.DateTime])));
$dt2.columns.add((new-object System.Data.DataColumn('LastDiffBackupDate', [System.DateTime])));
$dt2.columns.add((new-object System.Data.DataColumn('LastLogBackupDate' , [System.DateTime])));
foreach ($svr in $ServerInstance)
{ Write-verbose "processing:$svr"
try {
write-host "Processing $svr" -ForegroundColor Green
$dt=invoke-sqlcmd -ServerInstance $svr -Database master -Query $qry `
-QueryTimeout 120 -OutputAs DataTables;
$dt2.merge($dt); #append result to $dt2
}
catch
{
$r = $dt2.NewRow()
$r.ServerName = $svr;
$r.DBName = 'Server-Unaccessible';
$dt2.Rows.add($r);
write-Error "Error Processing$svr" ;
}
}#foreach $svr
Write-Output $dt2;
}
#####THIS WORKS GREAT PERFECTLY######
#$dt2 = Get-SQLDBInventory -serverinstance 'server1','server2','server3'
#it only pulls information for the 1st server and then exits.
$allservers = Get-DbaCmsRegServer -SqlInstance localhost -Group CMS_SQLGROUP | Select-Object -Unique -ExpandProperty ServerName
$dt2 = $allservers | get-sqldbinventory
Write-SqlTableData -ServerInstance localhost -DatabaseName DBAdmin -SchemaName dbo -TableName DBInventory -InputData $dt2;
December 23, 2019 at 2:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 23, 2019 at 9:13 am
Wouldn't you want to use a ForEach loop to process these? You're trying to pass the array to get-sqldbinventory but $ServerInstance is defined as a string, not an array.
I don't have the modules installed, but I would expect something more like:
foreach ($Server in $AllServers){
Get-SqlDbInventory -ServerInstance $Server.Name #don't know what the actual name of the property is
}
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 3, 2025 at 5:26 am
Find below script will help to get the SQL server inventory details.
create table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver
declare @SmoRoot nvarchar(512)
DECLARE @SN NVARCHAR(128)
DECLARE @sa NVARCHAR(128)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',N'ObjectName', @SN OUTPUT;
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SYSTEM\CurrentControlSet\services\MSSQLSERVER',N'ObjectName', @sa OUTPUT;
SELECT
@@SERVERNAME as ServerName,@@ServiceName as [InstanceName],
case when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '16.%' then 'SQL Server 2022'
varchar(100), SERVERPROPERTY(N'ProductVersion')) like '15.%' then 'SQL Server 2019'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '14.%' then 'SQL Server 2017'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '13.%' then 'SQL Server 2016'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '12.%' then 'SQL Server 2014'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '11.%' then 'SQL Server 2012'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.5%' then 'SQL Server 2008R2'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.0%' then 'SQL Server 2008'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.0%' then 'SQL Server 2008'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '9.0%' then 'SQL Server 2005'
else 'Not Found'
end as VersionName,
SERVERPROPERTY(N'ProductVersion') AS [Number],
SERVERPROPERTY('ProductLevel') AS SP,
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode],
@sa as [SQLService_Account],
@SN as [SQLAgent_Account],
(SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%')AS max_server_memory_MB,
(SELECT value_in_use FROM sys.configurations WHERE name like '%min server memory%')AS min_server_memory_MB,
(select Internal_Value from #SVer where Name = N'PhysicalMemory') AS [PhysicalMemory_In_MB],
convert(sysname, serverproperty(N'collation')) AS [Collation],
(select Value from #SVer where Name = N'ProductName') AS [Product],
(select Value from #SVer where Name = N'Language') AS [Language],
(SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) FROM master.sys.master_files) as Total_DB_Size_GB,
(SELECT COUNT(*) FROM sys.databases) as DB_Count,
@SmoRoot AS [RootDirectory],
SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath,
case when CAST(SERVERPROPERTY('IsClustered') AS bit) =1 then 'YES'
else 'NO' END
AS [IsClustered],
case when CAST(SERVERPROPERTY('IsClustered') AS bit)= 1 then (select serverproperty('ComputerNamePhysicalNetBIOS'))
else NULL END as Active_Node_Name,
(SELECT NodeName
FROM sys.dm_os_cluster_nodes where NodeName !=(select serverproperty('ComputerNamePhysicalNetBIOS'))) as Passive_Node_Name,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ISHADREnabled')) like 0 THEN 'Disabled'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ISHADREnabled')) like 1 THEN 'Enabled'
ELSE 'unknown'
END AS ISHADREnabled,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('HadrManagerStatus')) like 0 THEN 'Not started'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('HadrManagerStatus')) like 1 THEN 'Started'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('HadrManagerStatus')) like 2 THEN 'Not started'
ELSE 'unknown'
END AS HadrManagerStatus,
(select Value from #SVer where Name = N'Platform') AS [Platform],
(select Value from #SVer where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #SVer where Name = N'ProcessorCount') AS [Processors],
SERVERPROPERTY('FilestreamShareName') AS FilestreamShareName,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 1 THEN 'Personal or Desktop Engine'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 2 THEN 'Standard'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 3 THEN 'Enterprise'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 4 THEN 'Express'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 5 THEN 'SQL Database'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 6 THEN 'SQL Data Warehouse'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 8 THEN 'Managed Instance'
ELSE 'unknown'
END AS EngineEdition,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
SERVERPROPERTY('LicenseType') AS LicenseType
/*Add for versions greater than 2005
,(SELECT sqlserver_start_time FROM sys.dm_os_sys_info) as SQLServer_Start_Time
*/
drop table #SVer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy