Pull alerts from Solar Winds

[genehunter29009 (gene hunter)]

Code Share.
I use powershell to pull all new alerts from Solarwinds and here is the code.
First I check to see what the max alertid is in elasticsearch
then I query solarwinds db to find all alerts after this max alert
then push new alerts to elasticsearch.

I started doing this just for testing purposes. I am looking for a logstash way to do the same thing.
Sharing for common knowledge.

[warkolm (Mark Walkom)]

Thanks for sharing this! I'd help if you could format your code/logs/config using the </> button, or markdown style back ticks.

[genehunter29009 (gene hunter)]

############################################################
################ Import Modules

Import-Module Elastic.Console
import-module sqlserver
$serverinstance = "Sql Server Name here"

################ setup uri1 and ssl tls stuff

$uri1 = 'https://ElasticIPAddresshere:9200/swalerts1/_search?size=0'
$AllProtocols = [System.Net.SecurityProtocolType]'Ssl3,Tls,Tls11,Tls12'
[System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols

####################################################################
################ get max alertid from elasticsearch/swalerts1
################ so you can pull all alertid > $findalertid from solar winds

$body = '
{
"aggs" : {
"maxalert" : { "max" : { "field" : "alertid" } }
}
}'
$alertid = es $uri1 -Pretty -Method POST -Body $body -u elastic:changeme | convertfrom-json
[int]$findalertid = $alertid.aggregations.maxalert.value
$findalertid

######################################################################
################ query sql server for new records

$query = "
USE SolarWindsOrion;
SELECT
ah.AlertActiveID
-- ,AlertHistoryID
,ah.Message AS AlertMessage
,FORMAT(ah.Timestamp, 'yyyy-MM-ddTHH:mm:ssZ') as Timestamp
,ac.Name AS AlertName
,ao.RelatedNodeCaption
,ao.RelatedNodeId
--,'http://solarwinds.corp.lpl.com' + ao.RelatedNodeDetailsUrl
,ao.EntityType
,ao.EntityCaption
,ao.EntityNetObjectId
--,'http://solarwinds.corp.lpl.com' + ao.EntityDetailsUrl
FROM AlertHistory ah (nolock)
JOIN AlertObjects ao (nolock) ON ah.AlertObjectID = ao.AlertObjectID
JOIN AlertConfigurations ac (nolock) ON ao.AlertID = ac.AlertID
WHERE ah.AlertActiveID > $findalertid
"
$data = Invoke-Sqlcmd -ServerInstance $serverinstance -Query $query

######################################################################
################ post data to elasticsearch

foreach ($datum in $data )
{
$body='
{
"alertid": ' + $datum.AlertActiveID + ',
"message": "' + $datum.AlertMessage + '",
"timestamp": "' + $datum.Timestamp + '",
"name": "' + $datum.AlertName + '",
"nodecaption": "' + $datum.RelatedNodeCaption + '",
"nodeid": ' + $datum.RelatedNodeId + ',
"entitytype": "' + $datum.EntityType + '",
"entitycaption": "' + $datum.EntityCaption + '",
"entitiynetobjectid": "' + $datum.EntityNetObjectId + '"
}'
$body
es https://ElasticIPAddresshere:9200/swalerts1/_doc -Pretty -Method POST -Body $body -u elastic:changeme
}

####################################################

[ptamba]

@genehunter29009

I am looking for a logstash way to do the same thing.

you should be able to use jdbc input plugin for this. Use AlertActiveId as tracking_columb, assuming it’s incremental.