# Pull alerts from Solar Winds

**URL:** <https://discuss.elastic.co/t/pull-alerts-from-solar-winds/238493>\
**Category:** Logstash\
**Created:** [June 24, 2020, 1:49pm UTC](https://discuss.elastic.co/t/pull-alerts-from-solar-winds/238493 "2020-06-24T13:49:09Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![genehunter29009](https://avatars.discourse-cdn.com/v4/letter/g/45deac/32.png) [@genehunter29009](https://discuss.elastic.co/u/genehunter29009)\
**Post date:** [June 24, 2020, 1:49pm UTC](https://discuss.elastic.co/t/pull-alerts-from-solar-winds/238493/1 "2020-06-24T13:49:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [June 24, 2020, 10:15pm UTC](https://discuss.elastic.co/t/pull-alerts-from-solar-winds/238493/2 "2020-06-24T22:15:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![genehunter29009](https://avatars.discourse-cdn.com/v4/letter/g/45deac/32.png) [@genehunter29009](https://discuss.elastic.co/u/genehunter29009)\
**Post date:** [June 25, 2020, 10:18am UTC](https://discuss.elastic.co/t/pull-alerts-from-solar-winds/238493/3 "2020-06-25T10:18:29Z")

</div>

############################################################  
################ **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](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](http://solarwinds.corp.lpl.com)' + ao.RelatedNodeDetailsUrl  
,ao.EntityType  
,ao.EntityCaption  
,ao.EntityNetObjectId  
--,'[http://solarwinds.corp.lpl.com](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](https://ElasticIPAddresshere:9200/swalerts1/_doc) -Pretty -Method POST -Body $body -u elastic:changeme  
}

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

---

<div class="post-metadata">

**Author:** ![ptamba](https://avatars.discourse-cdn.com/v4/letter/p/7feea3/32.png) [@ptamba](https://discuss.elastic.co/u/ptamba)\
**Post date:** [June 25, 2020, 10:54am UTC](https://discuss.elastic.co/t/pull-alerts-from-solar-winds/238493/4 "2020-06-25T10:54:53Z")

</div>

> [@genehunter29009](#):
>
> I am looking for a logstash way to do the same thing.

you should be able to use [jdbc input plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-tracking_column) for this. Use AlertActiveId as tracking\_columb, assuming it’s incremental.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 23, 2020, 10:55am UTC](https://discuss.elastic.co/t/pull-alerts-from-solar-winds/238493/5 "2020-07-23T10:55:04Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.