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