Inserting Get-Counters to SQL table

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 5 months ago.

  • Author
    Posts
  • #6369

    by jagan at 2012-11-20 17:15:52

    Gurus, I want to collect Get-Counters from remote machines and dump that data into sql table. can you please tell me how to achieve this task.
    Please respond to this thread at your earliest convenience.

    $SQLCon.close()

    foreach ($row in $DataSet.tables["ServerName"].rows)
    {
    $DestinationServerName = $row.ServerName
    $DestinationServerName
    $GetCountersData = Get-Counter -Counter(Get-Counter -computername $DestinationServerName -ListSet PhysicalDisk).PathsWithInstances | Format-table -Auto $CapturedPerfmonData
    #$GetCountersData.CounterSamples

    foreach ($Counter in $CapturedPerfmonData)
    {
    $Path = $Counter.Path
    $Instancename = $Counter.InstanceName
    $CookedValue = $Coutner.CookedValue
    < #$dbconn = New-Object System.Data.SqlClient.SqlConnection("Data Source=LAIW2K8Db132P; Initial Catalog=LZDBMonitor; Integrated Security=SSPI")
    $dbconn.Open()
    $dbwrite = $dbconn.CreateCommand()
    $dbwrite.CommandText = "INSERT INTO ServerOSPhysicalDiskCounterValues ([ServerName] , [CounterPath] , [InstanceName] , [CookedValue] ) VALUES ($DestinationServerName, $Path, $InstanceName, $Cookedvalues)"
    $dbwrite.ExecuteNonQuery()
    $dbconn.Close()#>
    $Path
    $Instancename
    $CookedValue
    }
    }

    by Infradeploy at 2012-11-21 01:34:43

    The invoke-sql cmdlet is loads easier.
    You get that after installing the SQL management tools

    by Klaas at 2012-11-21 03:24:13

    Invoke-Sqlcmd would indeed be easier,
    But it is possible your way, if
    – you lose the Format cmdlet
    – you use the correct variable names: $counter != $coutner, $cookedvalue != $cookedvalues

    Try to debug or build your script in the shell step by step and find the step where it fails.

    by jagan at 2012-11-24 11:14:30

    klass, I tried using your suggestion. I am certainly doing something wrong... can you please help me with an example code which I can use it as a guideline to build my code?

    Thanks in advance!!

    by jagan at 2012-11-24 19:38:50

    FYI... this is what I wrote:

    #Set-ExecutionPolicy RemoteSigned

    ###########################################################################
    # Declare ServerName, DatabaseName and TableName to Get Server List
    ###########################################################################
    $SourceServerName = 'LAIW2k8DB132P'
    $SourceDatabaseName = 'LZDBMonitorMaster'
    #$SourceTablename = 'LookUp_ServerList_NonProd'
    $DestinationTableName = 'CollectServerPerformanceCountersPS'

    ###########################################################################
    # Declare Variables for Sending E-mails
    ###########################################################################

    $ToRecipient = "***@email.com"
    $From = "***@email.com"
    $SMTPServer = "smtp.email.com"
    $GetDate = get-date -format g

    ##########################################################################
    # Function to Dump Data from Get-Counters to DataTable[]
    ##########################################################################
    function Out-DataTable
    {
    [CmdletBinding()]
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)

    Begin
    {
    $dt = new-object Data.datatable
    $First = $true
    }
    Process
    {
    foreach ($object in $InputObject)
    {
    $DR = $DT.NewRow()
    foreach($property in $object.PsObject.get_properties())
    {
    if ($first)
    {
    $Col = new-object Data.DataColumn
    $Col.ColumnName = $property.Name.ToString()
    if ($property.value)
    {
    if ($property.value -isnot [System.DBNull])
    { $Col.DataType = $property.value.gettype() }
    }
    $DT.Columns.Add($Col)
    }
    if ($property.IsArray)
    { $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 }
    else { $DR.Item($property.Name) = $property.value }
    }
    $DT.Rows.Add($DR)
    $First = $false
    }
    }

    End
    {
    Write-Output @(,($dt))
    }

    }

    ##########################################################################
    # Function to Dump Data from DataTable[] to SQl Table
    ##########################################################################

    function Write-DataTable
    {
    [CmdletBinding()]
    param(
    [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
    [Parameter(Position=1, Mandatory=$true)] [string]$Database,
    [Parameter(Position=2, Mandatory=$true)] [string]$TableName,
    [Parameter(Position=3, Mandatory=$true,ValueFromPipeline = $true,ValueFromPipelinebyPropertyname = $true) ] $Data,
    [Parameter(Position=4, Mandatory=$false)] [string]$Username,
    [Parameter(Position=5, Mandatory=$false)] [string]$Password,
    [Parameter(Position=6, Mandatory=$false)] [Int32]$BatchSize=50000,
    [Parameter(Position=7, Mandatory=$false)] [Int32]$QueryTimeout=0,
    [Parameter(Position=8, Mandatory=$false)] [Int32]$ConnectionTimeout=15
    )

    $conn=new-object System.Data.SqlClient.SQLConnection

    if ($Username)
    { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
    else
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }

    $conn.ConnectionString=$ConnectionString

    try
    {
    $conn.Open()
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = $tableName
    $bulkCopy.BatchSize = $BatchSize
    $bulkCopy.BulkCopyTimeout = $QueryTimeOut
    $bulkCopy.WriteToServer($Data)
    $conn.Close()
    }
    catch
    {
    $ex = $_.Exception
    Write-Error "$ex.Message"
    continue
    }

    } #Write-DataTable

    ###########################################################################
    # Create SqlConnection object and define connection string
    ###########################################################################

    $SQLCon = New-Object System.Data.SqlClient.SqlConnection
    $SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true"

    ###########################################################################
    # Create SqlCommand object, define command text, and set the connection
    ###########################################################################

    $SQLCmd = New-Object System.Data.SqlClient.SqlCommand
    $SQLCmd.CommandText = "SELECT ServerName
    FROM [dbo].[LookUp_ServerList_NonProd] AS LUSLNP
    WHERE [SqlPingFlag] = 1"
    $SQLCmd.Connection = $SQLCon

    ###########################################################################
    # Create SqlDataAdapter object and set the command
    ###########################################################################

    $SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlDataAdapter.SelectCommand = $SQLCmd

    ###########################################################################
    # Create and fill the DataSet object
    ###########################################################################
    $DataSet = New-Object System.Data.DataSet
    $SqlDataAdapter.Fill($DataSet, "ServerName") | Out-Null

    ###########################################################################
    # Close the connection
    ###########################################################################
    $SQLCon.close()

    ###########################################################################
    # Loop Through Each Row in Data Set and Dump to SQL Table
    ###########################################################################

    foreach ($row in $DataSet.tables["ServerName"].rows)
    {
    $DestinationServerName = $row.ServerName
    $DestinationServerName
    $GetCounterData = Get-Counter -Counter(Get-Counter -computername $DestinationServerName -ListSet PhysicalDisk).PathsWithInstances
    $GetCounterData.CounterSamples | Select $DestinationServerName, Path, Instancename, CookedValue | Out-DataTable | Write-DataTable -ServerInstance $SourceServerName `
    -Database $SourceDatabaseName `
    -TableName $DestinationTableName `

    }

    by Klaas at 2012-11-25 10:31:25

    I think you mixed up the SQL SELECT and Powershell Select-Object. When you pipe countersamples to Select-Object, there is no $DestinationServerName. You can find that if you execute PS> $GetCounterData = Get-Counter -Counter(Get-Counter -computername localhost -ListSet PhysicalDisk).PathsWithInstances
    PS> $GetCounterData.CounterSamples
    in the shell.
    I would keep Chad's functions in a separate script and dotsource that in your script. There are already 7 versions of those functions. On the next change, you would only have to replace that file and not adapt every script where you ever used out-datatable or write-datatable.
    If you would have the SQLPS module you could replace about 130 lines of your script with Invoke-Sqlcmd. If not, you're on the right track. On first glance I think you're doing pretty good with the .Net objects sqlconnection, sqladapter, dataset except for the Select $Destinationservername.
    Select @{l='server';e={$DestinationServerName}}, Path, Instancename, CookedValue might do the trick.
    Besides that it's rather hard to troubleshoot if we don't know what errors you get or which part of the script fails.

    Also, be sure to download Chad's SQLPSX module, which has invoke-sqlcmd2 and perfcounter cmdlets in it: http://sqlpsx.codeplex.com/

You must be logged in to reply to this topic.