Category Archives: Admin Corner

Here is a simple PowerShell script for logging CPU usage of each SQL instance on a server to InfluxDB. Server hostname and SQL Instance name are tags, the rest are fields

# Build Uri 
$uri = "http://10.10.10.1:8086/write?db=telegraf" 

[array]$SQLData = Get-WmiObject Win32_Process -Filter "Name = 'sqlservr.exe'" | select ProcessId, commandline

$processor_time = (((Get-Counter '\Process(sqlservr*)\% processor time' -ErrorAction SilentlyContinue -MaxSamples 1 -Sampleinterval 1).Countersamples)) | select -Property Path,InstanceName,CookedValue,Status
$regex = "^[^:]+?_\s*"
$regex2 = "_([^/]+)$"
$hostname = hostname
[array]$SQL_Name = ""
[array]$SQL_PID = ""

#loop through all SQL instances
for ($i=0; $i -lt $SQLData.Length; $i++) {
#get the SQL Instance Name from the PID
$SQL_Name = ($SQLData[$i].commandline -split'-s')[1]                                
[int]$SQL_PID = $SQLData[$i].ProcessId

#extract the process name from the perf counter path (CPU)
$proc_time = $processor_time | Where-Object {(($_.InstanceName) -replace($regex,"") -eq $SQL_PID)}  | Select-Object -Property InstanceName, CookedValue
#if there is no process name next to a PID, just write dummy process name
if(($SQL_PID | measure-object -character | select -expandproperty characters) -lt 3) { $SQL_PID = 0 }

#DEBUG: 
($env:COMPUTERNAME +": "+  $SQL_Name + " PID: " + $SQL_PID + " Processor Time: "  + ([math]::Round($proc_time.CookedValue/$env:NUMBER_OF_PROCESSORS,2)) ) 
#generate the Influx logging calls
$measurement = "sql_cpu"
$tags = "host="+$hostname.ToString() + ",Instance="+$SQL_Name
$values = "PID="+$SQL_PID + ",CPU="+(([math]::Round($proc_time.CookedValue,2))/$env:NUMBER_OF_PROCESSORS)


# Write data to Influx DB 
Invoke-RestMethod -Uri $uri -Method Post -Body "$measurement,$tags $values" -TimeoutSec 10
}

Grafana Panel JSON for visualising the data:

{
  "type": "graph",
  "title": "Panel Title",
  "gridPos": {
    "x": 0,
    "y": 0,
    "w": 12,
    "h": 9
  },
  "id": 2,
  "targets": [
    {
      "refId": "A",
      "policy": "default",
      "resultFormat": "time_series",
      "orderByTime": "ASC",
      "tags": [],
      "groupBy": [
        {
          "type": "time",
          "params": [
            "$__interval"
          ]
        },
        {
          "type": "tag",
          "params": [
            "host"
          ]
        },
        {
          "type": "tag",
          "params": [
            "Instance"
          ]
        },
        {
          "type": "fill",
          "params": [
            "previous"
          ]
        }
      ],
      "select": [
        [
          {
            "type": "field",
            "params": [
              "CPU"
            ]
          },
          {
            "type": "mean",
            "params": []
          }
        ]
      ],
      "measurement": "sql_cpu",
      "alias": "$tag_host\\$tag_Instance"
    }
  ],
  "options": {
    "alertThreshold": true
  },
  "fieldConfig": {
    "defaults": {},
    "overrides": []
  },
  "pluginVersion": "7.5.10",
  "renderer": "flot",
  "yaxes": [
    {
      "label": null,
      "show": true,
      "logBase": 1,
      "min": null,
      "max": null,
      "format": "percent",
      "$$hashKey": "object:140"
    },
    {
      "label": null,
      "show": true,
      "logBase": 1,
      "min": null,
      "max": null,
      "format": "short",
      "$$hashKey": "object:141"
    }
  ],
  "xaxis": {
    "show": true,
    "mode": "time",
    "name": null,
    "values": [],
    "buckets": null
  },
  "yaxis": {
    "align": false,
    "alignLevel": null
  },
  "lines": true,
  "fill": 1,
  "linewidth": 1,
  "dashLength": 10,
  "spaceLength": 10,
  "pointradius": 2,
  "legend": {
    "show": true,
    "values": false,
    "min": false,
    "max": false,
    "current": false,
    "total": false,
    "avg": false
  },
  "nullPointMode": "null",
  "tooltip": {
    "value_type": "individual",
    "shared": true,
    "sort": 0
  },
  "aliasColors": {},
  "seriesOverrides": [],
  "thresholds": [],
  "timeRegions": [],
  "fillGradient": 0,
  "dashes": false,
  "hiddenSeries": false,
  "points": false,
  "bars": false,
  "stack": false,
  "percentage": false,
  "steppedLine": false,
  "timeFrom": null,
  "timeShift": null,
  "datasource": null
}