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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply