trying to pass a list of servers to a parameter

Welcome Forums General PowerShell Q&A trying to pass a list of servers to a parameter

Viewing 1 reply thread
  • Author
    Posts
    • #194798
      Participant
      Topics: 18
      Replies: 10
      Points: 102
      Rank: Participant

      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; 
      
      
    • #194834
      js
      Participant
      Topics: 27
      Replies: 739
      Points: 2,015
      Helping Hand
      Rank: Community Hero

      I don't know what get-sqldbinventory is. Unless its computername parameter accepts its value over the pipe byvalue, piping the names to it won't work.

Viewing 1 reply thread
  • You must be logged in to reply to this topic.