We have a lot of good data in Elasticsearch via running various Beats on our Windows servers. But to help create reports on capacity planning, i wanted more than just CPU / Memory / Disk usage over time for my SQL Servers. I also wanted to track database growth over time and then the tables within them.

Dumping all this data into SQL Server is of course easy enough to do (Especially with DBATools!) But i wanted to keep everything central in 1 source. so needed to load my data into Elasticsearch not just the Beats metrics.

Below are the basic steps to create a template push data in and then read it via Kibana

ElasticSearch Templates

First, as im collecting metrics by date, i wanted to create a rolling index (meaning i create a new index every day but they are linked together) for both database and table information.

To create my template i ran:

post _template/sqltable_template
    {
      "template" : "sqltable-*",
      "aliases" : {
        "sqltable" : {}
      },
      "settings" : {
        "index" : {
          "number_of_shards" : 1,
          "number_of_replicas" : 1
        }
      },
      "mappings" : {
        "sqltable_size" : {
          "properties" : {
            "TimeStamp" : {
              "type" : "date",
              "format" : "yyyy.MM.dd"
            },
            "server" : {
              "type" : "keyword"
            },
             "database" : {
              "type" : "keyword"
            },
            "schema" : {
              "type" : "keyword"
            },
            "table" : {
              "type" : "keyword"
            },
            "IndexSpaceUsed" : {
              "type" : "long"
            },
            "DataSpaceUsed" : {
              "type" : "long"
            },
            "RowCount" : {
              "type" : "long"
            }
          }
        }
      }
    }

This means that:

  • All indexes starting sqltable- will combine to make sqltable index
  • I have the following fields being recorded
    • TimeStamp
    • Server
    • Database
    • Table
    • IndexSpaceUsed
    • DataSpaceUsed
    • RowCount

I then did similar for the database template:

post _template/sqldb_template
{
  "template" : "sqldb-*",
  "aliases" : {
    "sqldb" : {}
  },
  "settings" : {
    "index" : {
      "number_of_shards" : 1,
      "number_of_replicas" : 1
    }
  },
  "mappings" : {
    "sqldb_size" : {
      "properties" : {
        "TimeStamp" : {
          "type" : "date",
          "format" : "yyyy.MM.dd"
        },
        "server" : {
          "type" : "keyword"
        },
         "database" : {
          "type" : "keyword"
        },
        "logicalName" : {
          "type" : "keyword"
        },
        "fieldType" : {
          "type" : "keyword"
        },
        "usedSpace" : {
          "type" : "long"
        }
      }
    }
  }
}

Push Data into ElasticSearch

Now i have my template i just need push data to my ElasticSearch cluster. To do this I just combined DBATools and a WebRequest, to end up with:

$servers = Get-DbaRegisteredServer -SqlInstance CMS -Group '01 - Production'

## create elasticsearch index name
$baseIndex = "sqldb-"
$timestamp = get-date -format "yyyy.MM.dd"
$index = $baseIndex + $timestamp

foreach($server in $servers)
{
Write-Output "Querying Server: $($server.Name) with DBATools function Get-DbaDatabaseFile"
$df = Get-DbaDatabaseFile -SqlInstance $server.Name
foreach ($d in $df)
{
Write-Output "Uploading results for database: $($server.Name).$($d.Database)"
 $customObject = [pscustomobject]@{
TimeStamp = $timestamp
Server = $d.SqlInstance
Database = $d.Database
LogicalName = $d.LogicalName
FileType = $d.TypeDescription
UsedSpace = $d.UsedSpace
}
$body = ConvertTo-Json $CustomObject -Compress
# using post as put needs Id
Invoke-RestMethod -Method Post -Uri "http://elasticsearch.:9200/$index/sqldb_size" -ContentType 'application/json' -Body $body

}
}

Loading Data in Kibana

After running that we should have code in Elasticsearch! and its time to visualise with Kibana:

In Kibana open Management -> Index Patterns and select “Create Index Pattern”

enter the index pattern (For my examples they are sqldb-* or sqltable-*) After entering the name it should auto detect the timestamp as a Time filter field name. Click Create

kibana01

Now when you go to Discovery or Visualize you can see your data

kibana02

From here you can create reports and dashboards combing all the data from Beats and your own indices to build up some good capacity reporting.

 

Advertisements