Posting metrics to the ELK stack via REST API

Recently I was working with the ELK stack and had a co-worker ask if there would be a way to have a scheduled task that ran a SQL query, and then output those values into ELK so we could build a visualization showing some of those metrics over time. It was a pretty straightforward data type, basically something like this:

	
{
    "post_date": "2020-01-15T13:14:59",
    "client_name": "ClientABCD",
    "number_of_events": 72
}

The intent here is that we would want to trend that data over time, per client, to see the "number_of_events" they had, and how that value increased or decreased over time.

The most common use case with ELK is to use Logstash or Filebeats to parse log files into the system. However, the ELK stack does provide a full REST API that you can use to directly interact with it. My thought was to create a Powershell script that would run a SQL query, and then iterate through the result set and manually POST each record to the ELK endpoint.

I ended up finding the Index API, which provided me with the syntax on how to make a POST to an Elasticsearch index. It's worth noting that in my ELK stack, the system was setup to automatically create indices if they didn't already exist when inserting a document. If this is NOT the case for your instance, my samples here will likely fail and you'll have to set the index up on your own.

I started with a simple test trying to POST my JSON body to my new index I wanted, "/metrictest/eventcounter":

	
$metrics_date = (Get-Date -Format "yyyy-MM-ddTHH:mm:ss").ToString()
    
$post_body_raw = @{
	post_date=$metrics_date;
    client_name='ClientABCD';
    number_of_events=155
}
    
$post_body_json = ConvertTo-Json ([System.Management.Automation.PSObject] $post_body_raw)
    
Invoke-WebRequest -UseBasicParsing -Uri http://your-elk-server-name:9200/metrictest/eventcounter -ContentType "application/json" -Method POST -Body $post_body_json

When I tried to run that, I got this:

	
Invoke-WebRequest : {"error":{"root_cause":[{"type":"security_exception","reason":"missing authentication credentials for REST request [/metrictest/eventcounter]","header":
{"WWW-Authenticate":"Basic realm=\"security\" charset=\"UTF-8\""}}],"type":"security_exception","reason":"missing authentication credentials     
for REST request [/metrictest/eventcounter]","header":{"WWW-Authenticate":"Basic realm=\"security\" charset=\"UTF-8\""}},"status":401}
At line:1 char:1                                                                                                                                                                       
+ Invoke-WebRequest -UseBasicParsing -Uri http://your-elk-server-name:9200/metri...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException 
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

Uh-oh! Looks like my ELK stack requires authentication! In my setup, ELK was configured for "native" authentication, which means that ELK controls the usernames and passwords. When configured that way, you can simply pass the username and password as a base64 encoded string in an authentication header.

Let's update the Powershell script to add that in:

	
$user = "elasticUser"
$password = "elasticPassword"
$credential = "${user}:${password}"
$credentialBytes = [System.Text.Encoding]::ASCII.GetBytes($credential)
$base64Credential = [System.Convert]::ToBase64String($credentialBytes)
$basicAuthHeader = "Basic $base64Credential"
$headers = @{ Authorization = $basicAuthHeader }

$metrics_date = (Get-Date -Format "yyyy-MM-ddTHH:mm:ss").ToString()
    
$post_body_raw = @{
	post_date=$metrics_date;
    client_name='ClientABCD';
    number_of_events=155
}
    
$post_body_json = ConvertTo-Json ([System.Management.Automation.PSObject] $post_body_raw)
    
Invoke-WebRequest -UseBasicParsing -Uri http://your-elk-server-name:9200/metrictest/eventcounter -ContentType "application/json" -Headers $headers -Method POST -Body $post_body_json

Success!

	
StatusCode        : 201
StatusDescription : Created
Content           : {"_index":"metrictest","_type":"eventcounter","_id":"dASMqm8BSqDFEHEt7te7","_version":1,"result":"created","_shards":{"total":2,"successful":2,"failed":0},"_seq_no":0,"_primary_term":1}
RawContent        : HTTP/1.1 201 Created
					Content-Length: 185
					Content-Type: application/json; charset=UTF-8
					Location: /metrictest/eventcounter/dASMqm8BSqDFEHEt7te7
					
					{"_index":"metrictest","_type":"eventcounter","_id"...   
Forms             :                                                                                                                                                                                              
Headers           : {[Content-Length, 185], [Content-Type, application/json; charset=UTF-8], [Location, /metrictest/eventcounter/dASMqm8BSqDFEHEt7te7]}  
Images            : {}
InputFields       : {}
Links             : {}
ParsedHtml        :
RawContentLength  : 185

Now we can go into Kibana and go to "Management --> Create Index Pattern" and add our "metrictest" index:

Select "post_date" as your Time Filter field

And then select that index pattern in the Discover section of Kibana and you'll be able to see your data:

Hopefully you find this useful. I just started digging into the ELK stack recently and there's definitely a ton of cool stuff there!

Thanks,
Justin