Need Query to Fetch The Data From a Report

Hi Folks!!

Background:

I have zero knowledge of solarwinds. I am writing a powershell script where I want to get the data from availability report (Node, Hostname, Availbility %, etc.). I am looking for a query to get the same.

My Solution:

I found below query but it only gives the report information.

query => SELECT JobsData FROM Orion.Report WHERE ReportID='1193'

output => [{"ActionDisplayname":["Copy (18) of Email"], "JobDescription":"", "JobDisplayName":"SW KAO Network Availbility- Monthly", "JobId":54, "SchedulesDisplayName":["Monthly"]}]

Could you please help me with query. I'll appreciate all the help.

Thanks,

Find more posts tagged with

Reports

query

orion

Database

solarwinds

thwack

PowerShell

sdk

Accepted answers

All comments

mesverrum

In SolarWinds you don't pull data from the report, you need to just query the raw data itself and basically do the same thing that the report does. With zero knowledge of solarwinds it is probably going to be really hard for you to write the necessary query on your own.

You'll probable want to start with this blog post to learn about the tool that helps you write queries

thwack.solarwinds.com/.../intro-to-swql-studio

GrWa

You could try this to view the SQL that is being used for a given report that may help lead to the data you want for your query.

Where is web based custom reports are stored and how to get SQL query for web based custom reports? - Report Lab - The SolarWinds Platform - THWACK

mesverrum

I'll throw in the comment that the API uses SWQL, which is slightly different than SQL. So seeing the actual SQL from the DB can be be kind of misleading about the actual query one would need to write to get data from the API.

adam.beedell

You want https://solarwinds.github.io/OrionSDK/swagger-ui/#/Query/Query /query or Get-SwisData depending on if you've a preference for modules or not

eg

$swis = connect-swis $params
$query =
"

whatever the query on the jobsdata report is

"
$results = get-swisdata -swis $swis -query $query

adam.beedell

Getting the data right from the report is fine too, but just not how i'd ever do it so idk

FormerMember

Thanks for the suggestion! I choose to go with querying raw data.

But I have a problem while fetching the custom properties.

SELECT TOP 100
    rt.NodeID,
    n.Caption,
    AVG(rt.Availability) AS AverageAvailability,
    MIN(rt.Availability) AS MinimumAvailability,
    MAX(rt.Availability) AS MaximumAvailability,
    COUNT(*) AS RecordCount,
    MIN(rt.DateTime) AS EarliestDateTime,
    MAX(rt.DateTime) AS LatestDateTime,
    n.CustomProperties.Tower AS [Tower]
FROM Orion.ResponseTime rt
INNER JOIN Orion.Nodes n ON n.NodeID = rt.NodeID
WHERE rt.DateTime > ADDDAY(-7, GETDATE())
GROUP BY rt.NodeID, n.Caption
ORDER BY AVG(rt.Availability) ASC

I searched and found we can use below query to get the custom properties. But it is not working out for me.

n.CustomProperties.Tower

Error: There is no such custom property as 'Tower'.

But when I checked

SELECT TOP 1000 Table, Field, IsForAlerting, IsForFiltering, IsForGrouping, IsForReporting, IsForEntityDetail, IsForAssetInventory
FROM Orion.CustomPropertyUsage

It shows that 'Tower' property actually exists.

adam.beedell

Is that SQL or SWQL?

FormerMember

Its SWQL. Below is my reference.

thwack.solarwinds.com/.../which-table-contains-node-the-availability-percentage