It took me some time to work out how to use ExecuteSQL from the orionsdk python module.
To save someone else the pain - here are some examples on how to use it properly.in python.
from pprint import pprint
# disables warnings about invalid certificates if you
# have a self signed certificate
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
from orionsdk import SwisClient
server = "server"
username = "you"
password = "you_are_awesome"
swis = SwisClient(server, username, password)
sqlquery = """
SELECT Id ,
EntityType,
Name,
DisplayName ,
DefaultThresholdOperator ,
RecalculationNeeded,
ThresholdOrder,
Unit
FROM SolarWindsOrion.dbo.ThresholdsNames
where ID = 91 or Name = 'Nodes.Stats.CpuLoad'
"""
parameters = None
maxrows = 0 # no limit
results = swis.invoke("Orion.Reporting","ExecuteSQL",sqlquery, parameters, maxrows, False )
pprint(results)
Which will output:
[
{'DefaultThresholdOperator': 1,
'DisplayName': 'CPU Load',
'EntityType': 'Orion.Nodes',
'Id': 82,
'Name': 'Nodes.Stats.CpuLoad',
'RecalculationNeeded': False,
'ThresholdOrder': 0,
'Unit': '%'},
{'DefaultThresholdOperator': 1,
'DisplayName': 'Received Interface Errors and Discards',
'EntityType': 'Orion.NPM.Interfaces',
'Id': 91,
'Name': 'NPM.Interfaces.Stats.InErrorsDiscards',
'RecalculationNeeded': False,
'ThresholdOrder': 0,
'Unit': '/hour'}
]
If you want to parameterize a SQL query, the query should have a MS SQL named parameter like @middlename and then you will feed it a dictionary that looks like { "middlename": "timothy" }
The same example above with properly escaped sql parameters:
from pprint import pprint
# disables warnings about invalid certificates if you
# have a self signed certificate
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
from orionsdk import SwisClient
server = "server"
username = "you"
password = "you_are_awesome"
swis = SwisClient(server, username, password)
sqlquery = """
SELECT Id ,
EntityType,
Name,
DisplayName ,
DefaultThresholdOperator ,
RecalculationNeeded,
ThresholdOrder,
Unit
FROM SolarWindsOrion.dbo.ThresholdsNames
WHERE ID = @firstid or Name = @alertname
"""
parameters = {
"firstid": 91,
"alertname": "Nodes.Stats.CpuLoad"
}
maxrows = 0 # no limit
results = swis.invoke("Orion.Reporting","ExecuteSQL",sqlquery, parameters, maxrows, False )
pprint(results)
