Friday, 8 January 2016

WMI Data Collector for DBA Monitoring and Alerts - Powershell by serverku

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. 
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("","

")
        
        
        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.
-- 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'

GO

Note: 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!
Posted by at No comments:
Labels: , , , , , , , ,
Subscribe to: Posts (Atom)