Muramatsu Sistemas

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString ="server=SERVIDOR;database=DATABASENAME;trusted_connection=true;"

$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Aspas="'"

$arrComputers = get-Content -Path "E:\ColetaContadoresWMI\Computers.txt"

$Contador = @{"Data"=$DataHora}

$Contador.Clear()

foreach ($strComputer in $arrComputers)

{

$SO =Get-WmiObject -query "Select Version from Win32_OperatingSystem" -computer $strComputer

$Queue =get-wmiobject -query "select Name,AvgDiskQueueLength from Win32_Perfformatteddata_perfdisk_physicaldisk where Name<>'_Total'" -computername $strComputer

foreach ($objItem in $Queue)

{

$ContadorSrv = $objItem.Name

$Contador[$ContadorSrv]=$objItem.AvgDiskQueueLength

$Valor=$objItem.AvgDiskQueueLength

$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv-AvgDiskQueueLength$Aspas,$Valor)"

$Command.ExecuteNonQuery()

}

$CPU =get-wmiobject -query "select PercentProcessorTime from Win32_Perfformatteddata_perfos_processor where name='_Total'" -computername $strComputer

foreach ($objItem in $CPU)

{

$ContadorSrv = "PercentProcessorTime"

$Contador["$ContadorSrv"]=$objItem.PercentProcessorTime

$Valor=$objItem.PercentProcessorTime

$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"

$Command.ExecuteNonQuery()

}

$Memoria=get-wmiobject -query "select AvailableMBytes from Win32_PerfFormattedData_PerfOS_Memory" -computername $strComputer

foreach ($objItem in $Memoria)

{

$ContadorSrv = "AvailableMBytes"

$Contador["$ContadorSrv"]=$objItem.AvailableMBytes

$Valor=$objItem.AvailableMBytes

$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"

$Command.ExecuteNonQuery()

}

$Espaco=Get-WmiObject -query "Select DeviceID,Freespace from Win32_logicaldisk where DriveType=3" -computername $strComputer

foreach ($objItem in $Espaco)

{

$ContadorSrv = $objItem.DeviceID

$Contador[$ContadorSrv]=$objItem.Freespace

$Valor=$objItem.Freespace

$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv-FreeSpace$Aspas,$Valor)"

$Command.ExecuteNonQuery()

}

if ($SO.version -like "6.*")

{

$Buffer =get-wmiobject -query "select Buffercachehitratio from Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager" -computername $strComputer |Select -Unique

foreach ($objItem in $Buffer)

{

$ContadorSrv = "Buffercachehitratio"

$Contador["$ContadorSrv"]=$objItem.Buffercachehitratio

$Valor=$objItem.Buffercachehitratio

$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"

$Command.ExecuteNonQuery()

}

$Stolen=get-wmiobject -query "Select Stolenpages from Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager" -computername $strComputer |Select -Unique

foreach ($objItem in $Stolen)

{

$ContadorSrv = "StolenPages"

$Contador["$ContadorSrv"]=$objItem.Stolenpages

$Valor=$objItem.Stolenpages

$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"

$Command.ExecuteNonQuery()

}

$TargetPages=get-wmiobject -query "Select TargetPages from Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager" -computername $strComputer |Select -Unique

foreach ($objItem in $TargetPages)

{

$ContadorSrv = "TargetPages"

$Contador["$ContadorSrv"]=$objItem.TargetPages

$Valor=$objItem.TargetPages

$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"

$Command.ExecuteNonQuery()

}

$TotalPages=get-wmiobject -query "Select Totalpages from Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager" -computername $strComputer |Select -Unique

foreach ($objItem in $TotalPages)

{

$ContadorSrv = "TotalPages"

$Contador["$ContadorSrv"]=$objItem.TotalPages

$Valor=$objItem.TotalPages

$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"

$Command.ExecuteNonQuery()

}

}

}

$Connection.close

Muito bem agora note que há um parâmetro que é alimentado por outro arquivo, semplismente crie-o onde desejar e aponte o path no script, este arquivo que é carregado são todos os servidores que você deseja coletar dados, então vamos supor que você precise monitorar os servidores Server1, Server2 e Server3, basta escrever o nome no arquivo e pronto.

Server1

Server2

Server3

Agora, com tudo configurados vamos voltar ao Management Studio e criar a tabela onde iremos armazenar os dados que coletamos

CREATE TABLE [dbo].[TB_CONTADORESWMI](

[Registro] [bigint] IDENTITY(1,1) NOT NULL,

[Name] [varchar](255) NULL,

[Value] [numeric](18, 0) NULL,

[Date] [datetime2](7) NULL,

[Server] [varchar](255) NULL

) ON [PRIMARY]

CREATE CLUSTERED INDEX [IX_TB_CONTADORESWMI] ON [dbo].[TB_CONTADORESWMI]

(

[Date] ASC,

[Server] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TB_CONTADORESWMI] ADD CONSTRAINT [DF_TB_CONTADORESWMI_Date] DEFAULT (getdate()) FOR [Date]