Need help adding a custom application/powershell query with results by time and date

Hi,

I am in need of pulling information from another sql server using the below sql query.

SELECT *

FROM

(SELECT XMLMessage.value('(/LogMessage//StateCode/node())[1]', 'nvarchar(max)') AS AlarmType, DATEADD(MINUTE,-300,MessageUTC) AS LocalDateTime FROM JournalLog WHERE MessageUTC >= DATEADD(day, -1, GETDATE())) AS INTAB

WHERE AlarmType = 'DoorForced'

ORDER BY LocalDateTime

DESC

any assistance would be greatly appropriated.

Find more posts tagged with

sql server query

Accepted answers

All comments

ebenditt1

Okay I was able to run this query with powershell but now how do I display the result in a solarwinds widget

---------------------------------------------------------------------------------------------

$dataSource = "server_ip"

$user = "solarwinds_name"

$pwd = "solarwinds_password"

$database = "db_name"

$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=True;"

$query = "SELECT * FROM (SELECT XMLMessage.value('(/LogMessage//StateCode/node())[1]', 'nvarchar(max)') AS AlarmType, DATEADD(MINUTE,-300,MessageUTC) AS LocalDateTime FROM JournalLog WHERE MessageUTC >= DATEADD(day, -1, GETDATE())) AS INTAB WHERE AlarmType = 'DoorForced'

ORDER BY LocalDateTime

DESC

$connection = New-Object System.Data.SqlClient.SqlConnection

$connection.ConnectionString = $connectionString

#$connection.ConnectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"

$connection.Open()

$command = $connection.CreateCommand()

$command.CommandText = $query

$result=$command.ExecuteReader()

$Datatable = New-Object System.Data.DataTable

$Datatable.load($result)

$Datatable | Export-Csv -Path "Alerts.txt" -NoTypeInformation

$Connection.Close()

---------------------------------------------------------------------------------------------

And I was also able to create a custom html widget with the following.

</iframe>

---------------------------------------------------------------------------------------------

However, this defeats the purpose of what we need SolarWinds to do. Is there no way to produce this query and see the results inside a proper widget and alert on them? I mean really I have to create a custom widget to display the results and then I have to create an alert to run the powershell script, and there is no way to monitor the results as I can not find how to do that yet. Can someone please help me as I am not expert and really need this.

ebenditt1

Here is the web snip-it code:

<%--

Generated by ASP2ASPX 4.6.

ASP2ASPX home page: http://www.netcoole.com

--%>

<%@ Page language="vb" Debug="true" AspCompat = "true"%>

<html>

<head>

'URL link to the Solarwinds Report

'http://solarwinds-web-server-IP-or-FQDN/Orion/Report.aspx?Report=Solarwinds_Statistic_-_Node_Polling_Method

'add the following parameter to the end of the URL link: &Printable=TRUE&accountid=userID&password=userPwd"

'userID is Solarwinds user account ID, userPwd is the account password. The account should be read only.

strURL = "https://solarwinds.com/Orion/Report.aspx?Report=Solarwinds_Statistic_-_Node_Polling_Method&Printable=TRUE&accountid=<userID>&password=<userPwd>"

'get the URL return text

strHttpText = GetTextFromUrl(strURL)

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

// Load the Visualization API and the piechart package.

google.load('visualization', '1.0', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.

google.setOnLoadCallback(drawChart);

// Callback that creates and populates a data table,

// instantiates the pie chart, passes in the data and

// draws it.

function getNumber(text, patt){

var start = text.match(patt);

if (start){

start = text.indexOf('>', start.index);

var temp = text.substring(start + 1);

var end = temp.match(/<\/a>/im);

if(end){

return temp.substring(0, end.index);

}

function getData(){

var body='<%= HttpUtility.JavaScriptStringEncode(strHttpText) %>';

var ping = getNumber(body, /href=\"http\:\/\/polling-ping\">/im);

var snmp1 = getNumber(body, /href=\"http\:\/\/polling-snmp1\">/im);

var snmp2 = getNumber(body, /href=\"http\:\/\/polling-snmp2\">/im);

var snmp3 = getNumber(body, /href=\"http\:\/\/polling-snmp3\">/im);

var wmi = getNumber(body, /href=\"http\:\/\/polling-wmi\">/im);

var total = ping*1+snmp1*1+snmp2*1+snmp3*1+wmi*1;

return {array:[['PING '+ping, ping*1],

['SNMP1 '+snmp1, snmp1*1],

['SNMP2 '+snmp2, snmp2*1],

['SNMP3 '+snmp3, snmp3*1],

['WMI '+wmi, wmi*1] ],

total: total};

}

function drawChart() {

// Create the data table.

var data = new google.visualization.DataTable();

data.addColumn('string', 'NodeStatus');

data.addColumn('number', 'Slices');

var temp = getData()

data.addRows(temp.array);

//var data = google.visualization.arrayToDataTable(temp.array);

// Set chart options

var options = {title: 'Total Nodes: '+temp.total,

width: 550,

height: 250,

is3D: true,

pieSliceText: 'percentage',

sliceVisibilityThreshold: 0,

pieStartAngle: -100,

slices: {0:{offset: 0.2, color: 'Magenta'},

1: {offset: 0.1, color: 'orange'},

2: {color: '#0099C6'},

3: {offset: 0.1, color: 'yellow'},

4: {offset: 0.2, color: 'blue'},

};

// Instantiate and draw our chart, passing in some options.

var chart = new google.visualization.PieChart(document.getElementById('chart_div'));

chart.draw(data, options);

}

</script>

</head>

<body>

</body>

</html>

Dim oXMLHTTP As Object ' MSXML2.ServerXMLHTTP.3.0

Dim strURL As String

Dim strHttpText As Object

Dim rexp01 As Regex

Dim matchs01 As MatchCollection

Dim match01 As Object

Dim rexp02 As Regex

Dim matchs02 As MatchCollection

Dim match02 As Object

Dim rexp03 As Regex

Dim matchs03 As MatchCollection

Dim match03 As Object

Dim rexp04 As Regex

Dim matchs04 As MatchCollection

Dim match04 As Object

Dim rexp05 As Regex

Dim matchs05 As MatchCollection

Dim match05 As Object

Dim iTotalNodesCount As Object

'Sub Function: get a URL return text

Function GetTextFromUrl(url As String) As Object

oXMLHTTP = Server.CreateObject ("MSXML2.ServerXMLHTTP.3.0")

oXMLHTTP.Open("GET", url, false)

oXMLHTTP.Send

If CInt(oXMLHTTP.Status) = 200 Then

GetTextFromUrl = oXMLHTTP.responseText

End IF

End Function

</script>