Storing and Visualizing Time Series Data with InfluxDB and Universal Dashboard
InfluxDB is a time series database for storing and processing metrics and events. It’s open-source, cross-platform, has a small footprint and stellar performance when dealing with high throughput event data. In this blog post we will look at how to setup InfluxDB on Windows, send performance event data to the platform and build a dashboard to expose the data as easy to view charts using Universal Dashboard.
Installing InfluxDB
To install InfluxDB, you’ll need to visit their download page. There is a ZIP file that contains all the binaries necessary to run the database and execute commands against it. For the purpose of this blog post, I downloaded the 1.7.1 version found here: https://dl.influxdata.com/influxdb/releases/influxdb-1.7.1_windows_amd64.zip.
Once downloaded, I extracted the ZIP file to a folder. The binary list is shown below and clocks in right under 100 MB.
The InfluxDB database daemon is influxd.exe. To start the database, you need to run it from the command line with the -config flag and specify a config file. There is a default config file in the same directory.
influxd.exe -config ./influxdb.conf
You should see the executable start and begin listening for requests.
Configuring InfluxDB
To configure InfluxDB, you’ll need to create a database. The influx.exe command line tool can be used to connect to your database and execute commands. InfluxDB has some elements of SQL and it’s own query language.
The first step is to run influx.exe and create a database. This can be done with the CREATE DATABASE command. For the purpose of this example, I’ve created the performance_data database.
E:\influxdb>influx
Connected to http://localhost:8086 version 1.7.1
InfluxDB shell version: 1.7.1
Enter an InfluxQL query
> CREATE DATABASE performance_data
> SHOW DATABASES
name: databases
name
----
_internal
performance_data
Now that we have a database, we can begin to write data to it. You can write data directly from influx.exe but in this example, we will be using their REST API to populate the database with data from PowerShell.
Sending Data from PowerShell
The InfluxDB daemon exposes a REST API that listens on port 8086 by default. You can send data to the system with Invoke-RestMethod and the InfluxDB Line Protocol.
The syntax of the payload should follow this structure.
<measurement>[,<tag_key>=<tag_value>[,<tag_key>=<tag_value>]] <field_key>=<field_value>[,<field_key>=<field_value>] [<timestamp>]
The measurement is the “table” the particular data belongs to. A “tag” is an indexed value that identifies this measurement. It could be the host name of a machine or a performance counter name. A field is a non-indexed value of data. This is typically your event data. This could be your % of disk time usage or bytes per second in network reads.
Using Get-Counter we can collect data from a specified performance counter. We will then aggregate all instances of the counter values into a single value and send it to InfluxDB.
$InfluxUrl = "http://localhost:8086/write?db=performance_data"
$Counter = '\Processor(_Total)\% Processor Time'
$Value = 0
(Get-Counter $Counter).CounterSamples | ForEach-Object {
$Value += $_.CookedValue
}
Invoke-RestMethod -Method POST -Uri $InfluxUrl -Body "counter,host=$ENV:COMPUTERNAME,counter=$($Counter.Replace(' ', '\ ')) value=$value"
If the REST API doesn’t return an error, it worked. The tag and field values may require character escapes if they contain spaces or other special characters.
I’ve set tags for the host and the counter. If we pop back to influx.exe and query the database, you can see the counter values.
> USE performance_data
Using database performance_data
> SELECT * FROM counter
name: counter
time counter host value
---- ------- ---- -----
1544065849105458300 \Processor(_Total)\% Processor Time DESKTOP-RB1QJB4 0.858510962407
The timestamp is automatically populated to Unix Epoch time in nanoseconds. You can optionally specify a time yourself. This is handy for grouping metrics together that happened around the same time and you don’t require nanosecond precision.
Querying Data from PowerShell
Now that we’ve populated our database with a metric, we can use the same REST API to query the values of these metrics. I’ve created a simple helper method to create a PowerShell-friendly hashtable around the InfluxDB JSON return values.
Get-InfluxDB queries the InfluxDB REST API and returns the value as a parsed hashtable. It also converts the time string into a .NET DateTime.
function Get-InfluxDB {
param(
[Parameter()]
$Url = 'http://localhost:8086/query?db=performance_data',
[Parameter()]
$Query
)
$Results = Invoke-RestMethod -Uri "$Url&q=$Query"
foreach($series in $results.results.series) {
$ResultSeries = @{
Fields = @()
}
foreach($tag in $series.tags.PSObject.Properties) {
$ResultSeries[$tag.Name] = $Tag.Value
}
$Columns = $series.columns
foreach($value in $series.values) {
$Result = @{}
for($i = 0; $i -lt $Columns.Length; $i++) {
if ($Columns[$i] -eq 'time') {
$result.time = [DateTime]$value[$i]
} else {
$Result[$columns[$i]] = $value[$i]
}
}
$ResultSeries.fields += $result
}
$ResultSeries
}
}
The result data contains the measurement, tags and fields return by the query. You can use the same query language that you used in influx.exe to query through the REST API. Invoking the query from our previous example from PowerShell looks like this.
PS C:\> ipmo C:\Users\adamr\Desktop\influxdb.psm1
PS C:\> Get-InfluxDb -Query 'SELECT * FROM counter'
Name Value
---- -----
Fields {System.Collections.Hashtable}
PS C:\> $Data = Get-InfluxDb -Query 'SELECT * FROM counter'
PS C:\> $Data.Fields
Name Value
---- -----
counter \Processor(_Total)\% Processor Time
time 12/5/2018 8:10:49 PM
value 0.858510962407
host DESKTOP-RB1QJB4
Now that we can access InfluxDB from PowerShell, let’s look at integrating it into Universal Dashboard.
Collecting Data with Universal Dashboard
Using the scheduled endpoint feature of Universal Dashboard, we can schedule an endpoint to run on an interval. Let’s run our collection endpoint every five seconds.
$Schedule = New-UDEndpointSchedule -Every 5 -Second
$Endpoint = New-UDEndpoint -Schedule $Schedule -Endpoint { }
Within the endpoint, let’s query some performance metric data and send it to InfluxDB. We use our own timestamp to ensure that all our metrics are stored with the same time value.
$InfluxUrl = "http://localhost:8086/write?db=performance_data"
#Convert from millesecond to nanosecond
$TimeStamp = [DateTimeOffset]::Now.ToUnixTimeMilliseconds() * 1000000
$PerformanceStats = @(
'\Processor(_Total)\% Processor Time'
'\memory\% committed bytes in use'
'\physicaldisk(_total)\% disk time'
)
foreach($PerformanceStat in $PerformanceStats) {
$Value = 0
(Get-Counter $PerformanceStat).CounterSamples | ForEach-Object {
$Value += $_.CookedValue
}
Invoke-RestMethod -Method POST -Uri $InfluxUrl -Body "counter,host=$ENV:COMPUTERNAME,counter=$($PerformanceStat.Replace(' ', '\ ')) value=$value $TimeStamp"
}
In the above example, we are select three performance counters, query their current value and send that result into InfluxDB. You’ll be able to query the counter measurement to see your performance metrics.
Now that we have our data in InfluxDB, let’s create a chart to display it on our dashboard.
Display InfluxDB data in Universal Dashboard
The first step is to query InfluxDB for our data. Again, we can use Get-InfluxDB to retrieve data from our measurement. Let’s select all counters’ data that we received in the last five minutes and group by counter.
$Data = Get-InfluxDb -Query 'SELECT * FROM counter WHERE time > now() - 5m GROUP BY counter'
Now that we have our data, let’s transform it into a format that the Nivo Chart understands. The line chart requires hashtables with x and y values for each point in the chart. We can transform the InfluxDB data like this.
$ChartData = @()
foreach($series in $data) {
$SeriesData = @{
id = $series.counter
data = @()
}
foreach($field in $Series.Fields) {
$SeriesData.data += @{
x = $field.time
y = $field.value
}
}
$ChartData += $SeriesData
}
Now that the data is in the correct format, we can create our chart. We can set the bottom axis to rotate the labels 90 degrees, set some various styling parameters and provide the data.
$BottomAxis = New-UDNivoChartAxisOptions -TickRotation 90
New-UDNivoChart -Data $ChartData -Id "performanceStats" -Line -Responsive -MarginBottom 50 -MarginTop 50 -MarginRight 110 -MarginLeft 60 -YScaleMax 100 -YScaleMin 0 -EnableArea -AxisBottom $BottomAxis -Colors 'paired'
To ensure that the chart updates on a regular interval, we place the chart and data look up in an element that autorefreshs.
New-UDElement -Tag 'div' -Attributes @{ style = @{ "height" = '400px'}} -AutoRefresh -RefreshInterval 5 -Endpoint {
# Chart code (don't worry the whole thing is linked at the bottom)
}
Now, we have a pretty chart that is displaying data from InfluxDB.
Conclusion
InfluxDB is really neat and really easy to integrate with PowerShell and Universal Dashboard. Storing your data persistently provides a much more useful tool than existing UDMonitors as page reloads do not affect the data that is shown in the chart.
For more information on InfluxDB, visit their documentation.
For a full example of the InfluxDB integration with Universal Dashboard, download this Gist and get up and running. You’ll need at least Universal Dashboard 2.2.0-beta2 and the (very) small InfluxDB PowerShell module I created.
The example includes the metrics I have shown in this post along with an example of a stream chart with network information.