Sometime before i shared a script to collect data for SQL Server and databases using a power-shell script. Now i am sharing a same but it is for WMI data collectors instead of SQL data, which used for reporting, alert and monitoring purpose for all the Servers. Let me share a script here.
How to use?
1. Create a DBARepository database and following tables there.
[dbo].[WMIServers] - Includes Servers for which you want collect the wmi data
[dbo].[WMICollectors] - Includes the queries with particular schedule which collect the wmi data from specified servers, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug
2. Create following PS1 file and schedule in SQL Server Agent or Windows Task.
")
if($Result -ne $null -and $Result.Rows.Count -gt 0)
{
$emailFrom = $FromEmail
$subject = $WMIName
$emailbody = $results
$message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
$message.IsBodyHTML = $true
$smtpServer = $smtp
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}
}
$Result.Clear()
$Result.Dispose()
$results = $null
$dt.clear()
$dt.Dispose()
$Columns = ""
$Column = ""
IF ($PostExecScript -ne $null -and $PostExecScript -ne "")
{
$PostDCSQL = $dccn.CreateCommand()
$PostDCSQL.CommandText = $PostExecScript
$PostDCSQL.ExecuteNonQuery()
}
$NextRunUpdateScript = "UPDATE [dbo].[WMICollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate]))
WHERE WMIName = "+ "'" + $WMIName + "'"
$NextRunSQL = $dccn.CreateCommand()
$NextRunSQL.CommandText = $NextRunUpdateScript
$NextRunSQL.ExecuteNonQuery()
}
Catch
{
# Write-Host "Error Occured - Script"
$ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message
$ErrorMessage = $ErrorMessage.replace("'","''")
$CounterName = $Counter + $WMIName
$ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"',
GETDATE()"
$ErrorLogSQL = $dccn.CreateCommand()
$ErrorLogSQL.CommandText = $ErrorLogScript
$ErrorLogSQL.ExecuteNonQuery()
$NextRunUpdateScript = "UPDATE [dbo].[WMICollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate]))
WHERE WMIName = "+ "'" + $WMIName + "'"
$NextRunSQL = $dccn.CreateCommand()
$NextRunSQL.CommandText = $NextRunUpdateScript
$NextRunSQL.ExecuteNonQuery()
}
}
}
}
Catch
{
# Write-Host "Error Occured - Main"
$ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message
$ErrorMessage = $ErrorMessage.replace("'","''")
$CounterName = $Counter + $WMIName
$ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"',
GETDATE()"
$ErrorLogSQL = $dccn.CreateCommand()
$ErrorLogSQL.CommandText = $ErrorLogScript
$ErrorLogSQL.ExecuteNonQuery()
}
Finally
{
$dccn.Close()
}
3. Sample collector tables and collector queries.
How to use?
1. Create a DBARepository database and following tables there.
CREATE DATABASE [DBARepository] GO USE [DBARepository] GO CREATE TABLE [dbo].[WMIServers]( [InstanceID] [smallint] IDENTITY(1,1) NOT NULL, [ServerName] [varchar](100) NULL, [IsActive] [bit] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[WMICollectors]( [WMIID] [smallint] IDENTITY(1,1) NOT NULL, [WMIName] [varchar](100) NULL, [WMIItems] [varchar](4000) NULL, [PostExecScript] [varchar](4000) NULL, [IsActive] [bit] NULL, [Schedule] [varchar](30) NULL, [Occurrence] [smallint] NULL, [StartDate] [datetime] NULL, [LastRun] [datetime] NULL, [NextRun] [datetime] NULL, [InstanceIDs] [varchar](20) NULL, [WMITable] [varchar](50) NULL, [EmailTo] [varchar](100) NULL, [WMIScript] [varchar](4000) NULL, [smtp] [varchar](30) NULL, [FromEmail] [varchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[DBAErrorLog]( [CounterName] [varchar](200) NULL, [ServerName] [varchar](100) NULL, [ErrorMessage] [varchar](4000) NULL, [ErrorDate] [datetime] NULL, [IsSolved] [bit] NULL ) ON [PRIMARY] GO
[dbo].[WMIServers] - Includes Servers for which you want collect the wmi data
[dbo].[WMICollectors] - Includes the queries with particular schedule which collect the wmi data from specified servers, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug
2. Create following PS1 file and schedule in SQL Server Agent or Windows Task.
<#
File : WMICollector.ps1
Created By : Paresh Prajapati
Created date : 01/09/2015
Purpose : Collect a WMI data of specified servers using WMI queries
#>
Try
{
$Counter = 'WMICollectors - '
$DCInstance = '.'
$DCDatabase = 'dbarepository'
$DCQuery = "SELECT [WMIName],[WMIScript],[WMIItems],[InstanceIDs],[Schedule],[Occurrence],[WMITable],[EmailTo],[smtp],[FromEmail],[PostExecScript] FROM [WMICollectors]
WHERE IsActive = 1 AND InstanceIDs IS NOT NULL AND StartDate <= GETDATE() AND ISNULL(NextRun,0) <= GETDATE()"
$dccn=new-object System.Data.SqlClient.SQLConnection
#$dcconstring = "Server=$DCInstance;Database=$DCDatabase;user id=$DCUserName;password=$DCPassword"
$dcconstring = "Server=$DCInstance;Database=$DCDatabase;Integrated Security=sspi"
#$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Username $DCUserName -Password $DCPassword -Query $DCQuery
$WMIScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $DCQuery
$dccn.ConnectionString=$dcconstring
$dccn.Open()
if ($WMIScripts.Count -ne 0)
{
foreach ($WMIScript in $WMIScripts)
{
Try
{
$dt = new-object System.Data.DataTable
$Result = New-Object System.Data.DataTable
$Columns = ""
$Column = ""
$results = ""
$InstanceIDs = $WMIScript.InstanceIDs.ToString()
$WMIName = $WMIScript.WMIName.ToString()
$Script = $WMIScript.WMIscript
$WMIItems = $WMIScript.WMIItems
$EmailTo = $WMIScript.EmailTo.ToString()
$Schedule = $WMIScript.Schedule.ToString()
$Occurrence = $WMIScript.Occurrence.ToString()
$WMITable = $WMIScript.WMITable.ToString()
$smtp = $WMIScript.smtp.ToString()
$FromEmail = $WMIScript.FromEmail.ToString()
$PostExecScript = $WMIScript.PostExecScript.ToString()
IF ($InstanceIDs -eq "-1")
{
$Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] = -1"
}
ELSEIF ($InstanceIDs -eq "0")
{
$Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] > 0"
}
ELSE
{
$Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] > 0 AND [InstanceID] IN (" + $InstanceIDs + ")"
}
$Instances = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $Query
$Columns = $WMIItems
# Creating a data table : Start
# Adding a ServerName column to data table
$Columns = "ServerName" + "," + $Columns
$Column = $WMIItems.split(',')
if ($emailTo -ne $null -and $emailTo -ne "")
{
$dt.columns.add("ServerName") | Out-Null
for($j=0;$j -lt $Column.Count; $j++)
{
$Col = $Column[$j]
$dt.columns.add($Col) | Out-Null
}
}
# Creating a data table : end
foreach ($Instance in $Instances)
{
Try
{
$Server = $Instance.ServerName
$WmiQuery = $Script
$Expression = $WMIScript.WMIItems
$Values = ""
$wi = Get-WmiObject -ComputerName $Server -query $WmiQuery # | Format-Table -Auto # $Expression
# Filling a SQL table from array : Start
if ($WMITable -ne $null -and $WMITable -ne "")
{
for($i=0;$i -lt $wi.Count; $i++)
{
$Values = ""
for($j=0;$j -lt $Column.Count; $j++)
{
$Col = $Column[$j]
$Values = $Values + "'" + $($wi.Item($i)).$Col + "'" + ","
}
$Values = $Values.substring(0,$Values.Length-1)
$Values = "'" + $Server + "'" + "," + $Values
$WmiCommand ="INSERT into "+$WMITable+" ("+$Columns+") VALUES ("+$Values+")"
$WQL = $dccn.CreateCommand()
$WQL.CommandText = $WmiCommand
$WQL.ExecuteNonQuery()| out-null
}
}
# Filling a SQL table from array : End
# Filling a data table from array : Start
if ($emailTo -ne $null -and $emailTo -ne "")
{
for($i=0;$i -lt $wi.Count; $i++)
{
$row = $dt.NewRow()
$row.ServerName = $Server
for($j=0;$j -lt $Column.Count; $j++)
{
$Col = $Column[$j]
$row.$Col = $($wi.Item($i)).$Col
}
$dt.rows.add($row) | Out-Null
}
if($Result -eq $null)
{
$Result = $dt.Clone()
$Result.Clear()
}
$Result.Merge($dt)
$dt.Clear()
}
# Filling a data table from array : End
}
Catch
{
# Write-Host "Error Occured - Instance"
$ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message
$ErrorMessage = $ErrorMessage.replace("'","''")
$CounterName = $Counter + $WMIName
$ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"',
GETDATE()"
$ErrorLogSQL = $dccn.CreateCommand()
$ErrorLogSQL.CommandText = $ErrorLogScript
$ErrorLogSQL.ExecuteNonQuery()
}
}
if ($emailTo -ne $null -and $emailTo -ne "")
{
$results = $result | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray| ConvertTo-Html | out-string
$results = $results.Replace("","-- Master Records of servers and databases SET IDENTITY_INSERT WMIServers ON INSERT INTO [dbo].[WMIServers] ( [InstanceID], [ServerName], [IsActive] ) SELECT 1,'Server1', 1 UNION SELECT 2,'Server2', 1 SET IDENTITY_INSERT WMIServers OFF GO -- Wmi Collector sample table -- Add ServerName always first to get server name CREATE TABLE [dbo].[DiskInfo]( [ServerName] [varchar](50) NULL, [Name] [varchar](50) NULL, [VolumeName] [varchar](50) NULL, [Compressed] [varchar](10) NULL, [DriveType] [varchar](50) NULL, [FreeSpace] [bigint] NULL ) ON [PRIMARY] GO -- Collector sample records INSERT INTO [dbo].[WMICollectors] ( [WMIName] , [WMIScript], [WMIItems], [PostExecScript], [IsActive], [Schedule], [Occurrence], [StartDate], [LastRun], [NextRun], [InstanceIDs], [WMITable], [EmailTo], [smtp], [FromEmail] ) SELECT 'Disk Info', 'SELECT * FROM Win32_LogicalDisk', 'Name,VolumeName,Compressed,DriveType,FreeSpace', NULL, 1, 'day', 1, GETDATE(), NULL, NULL, '1,2', 'DiskInfo', 'prajapatipareshm@gmail.com', 'sampleSmtp.com' 'prajapatipareshm@gmail.com' GONote: WMI Queries Items and WMI tables must have same number of columns with addition of Server Name
- [WMIName]: Name of WMI data collector
- [WMIScript] : WMI Query
- [WMIItems] : Name of WMI query columns or items which you want get
- [PostExecScript] : Additional script which can be performed on collected data
- [IsActive]: Flag to data collector script to run or not
- [Schedule]: When script will run, It has possible values are Year, Month, Week, Day, Hour, Minute, Second
- [Occurrence] : When it will occur or frequency
- [StartDate] : Start date and time of collector counter
- [LastRun] : When it last ran, It will update by PS1 script
- [NextRun]: Next schedule to run data collector, It will be calculated by PS1 script
- [InstanceIDs]: Comma separated InstancesIds where script will be executed to collect data
- [smtp]: smtp address to send an email
- [EmailTo]: To receive an email for WMI data
- [FromEmail]: From Email, an email will send
You can add more WMI collector tables and queries with particular schedule which you want to capture. This script I have created for need for monitoring,alert and reporting. Still, I am enhancing it, you can also share your ideas for its enhancement. Enjoy Data Collector!
Comments
Post a Comment