Build a report using sql

I'm new to running sql queries and I need to run a report that i believe it only can be done by sql query. this report will be use as View / dashboard.

Can someone help me build a query that will return the following:

- Node IP Address

- Node Name

- Node Group

- Node Status (down only)

- Time Node Down

I really appreciate any help you can provide.

thank you

Find more posts tagged with

sql reporting

swsql

swsql report

npm reports

Accepted answers

yaquaholic

If you are new to SQL, start by having a look over sites like - W3schools SQL Tutorial and have a good Google, there are plenty of good resources out there and by picking up the basics, you will get a much better understanding of how the Orion DB works for doing so.

Add to that that the native API language of Orion is SolarWinds Query Language (SWQL), which has very SQL like syntax.

Use SolarWinds Query Language (SWQL - SWIS) - SolarWinds Worldwide, LLC. Help and Support

If you use the Database Manager (part of the Orion install), you can easily connect to your database.

Use Database Manager to view the SolarWinds database - SolarWinds Worldwide, LLC. Help and Support

Have a look at the Nodes table, for each of the (node) Custom Properties you will find a corresponding column name. Simply add these to your SQL script. You could also use MS SQL Studio (usually installed with your SQL database) or many other 3rd party tools.

To explain the SQL a bit more for you:

SELECT n.IP_Address, n.Caption, --Get the Nodes name and IP address,

CASE n.Status --CASE translates (in this example) numbers to text

WHEN 1 Then 'Up' --

WHEN 2 THEN 'Down' --

WHEN 9 THEN 'Unmanaged' --

WHEN 11 THEN 'External' --

END AS Status, --END the CASE statement

a.TriggerTimeStamp AS Since --with the Active Alert timestamp - when the device went down

FROM Nodes n --Using Nodes view fisrt

INNER JOIN AlertStatusView a --Then join the AlertStatusView table

ON n.NodeID = a.ActiveObject --matching Nodes.NodeID to AlertStatusView.ActiveObject (which is also the NodeID)

WHERE n.Status = 2 --WHERE Node Status = Down

AND a.AlertMessage LIKE 'The Device % is down%' --AND the alert message is something like "Device Down"

--% is an SQL wildcard character

The n.Caption Vs Nodes.Caption is just an abbreviation, defined when you call the tables in the SQL.

"From Nodes n"

Where 'n' is the abbreviation for 'Nodes', so n.Caption and Nodes.Caption are the same, just saves some typing.

The timestamp is prefixed with an 'a', so again this is an abbreviation.

If you look, at the SQL script, you will see that I have joined a table:

INNER JOIN AlertStatusView a

ON n.NodeID = a.ActiveObject

This matches the entries, by using the NodeID, which happens to correspond with the AlertStatusView.ActiveObject, between the two tables.

So when I select nodes which are n.Status = 2 (aka down), it retrieves the nodes name, IP, status, it also ties in the active alert for the node down

Links to help: SQL Joins

I hope it helps!

All comments

yaquaholic

You need to look at the Nodes (view) firstly, here you will find the device IP addresses, names, statuses and any custom properties (Groups).

You then need to find the active alerts and match them up, I found that the AlertMessages from the AlertStatusView worked quite nicely (you may need to ensure that the text matches your AlertMessage text correctly).

SQL

SELECT n.IP_Address, n.Caption,

CASE n.Status

WHEN 1 Then 'Up'

WHEN 2 THEN 'Down'

WHEN 9 THEN 'Unmanaged'

WHEN 11 THEN 'External'

END AS Status,

a.TriggerTimeStamp AS Since

FROM Nodes n

INNER JOIN AlertStatusView a

ON n.NodeID = a.ActiveObject

WHERE n.Status = 2

AND a.AlertMessage LIKE 'The Device % is down%'

Output:

IP_Address Caption Status Since

x.y.z.101 ABC01XYZ01 Down 2017-12-11 11:00:32.733

You might want to add a few other items in there, such as URL links (for hyperlinks in the report), or status or vendor icons.

Have a good look at the Nodes table, it uses NodeID for cross linking tables and is usually the best place to start when exploring the database.

This might not work straight away for you, but hopefully it will give you a head start, and let you work it out for yourself.

Regards,

Rich

yaquaholic

Without out knowing your custom properties, I can only guess at what your Groups are...

trimulya

yes, we have custom properties such as Region A, Region B to Region I

anyway, what is the different of Nodes.Caption and n.Caption?

is a.TriggerTimeStamp is a table name in Solarwinds database?

because i'm not sure where i can find this entry.

thank you

yaquaholic

Add to that that the native API language of Orion is SolarWinds Query Language (SWQL), which has very SQL like syntax.

Use SolarWinds Query Language (SWQL - SWIS) - SolarWinds Worldwide, LLC. Help and Support

If you use the Database Manager (part of the Orion install), you can easily connect to your database.

Use Database Manager to view the SolarWinds database - SolarWinds Worldwide, LLC. Help and Support

To explain the SQL a bit more for you:

SELECT n.IP_Address, n.Caption, --Get the Nodes name and IP address,

CASE n.Status --CASE translates (in this example) numbers to text

WHEN 1 Then 'Up' --

WHEN 2 THEN 'Down' --

WHEN 9 THEN 'Unmanaged' --

WHEN 11 THEN 'External' --

END AS Status, --END the CASE statement

a.TriggerTimeStamp AS Since --with the Active Alert timestamp - when the device went down

FROM Nodes n --Using Nodes view fisrt

INNER JOIN AlertStatusView a --Then join the AlertStatusView table

ON n.NodeID = a.ActiveObject --matching Nodes.NodeID to AlertStatusView.ActiveObject (which is also the NodeID)

WHERE n.Status = 2 --WHERE Node Status = Down

AND a.AlertMessage LIKE 'The Device % is down%' --AND the alert message is something like "Device Down"

--% is an SQL wildcard character

The n.Caption Vs Nodes.Caption is just an abbreviation, defined when you call the tables in the SQL.

"From Nodes n"

Where 'n' is the abbreviation for 'Nodes', so n.Caption and Nodes.Caption are the same, just saves some typing.

The timestamp is prefixed with an 'a', so again this is an abbreviation.

If you look, at the SQL script, you will see that I have joined a table:

INNER JOIN AlertStatusView a

ON n.NodeID = a.ActiveObject

This matches the entries, by using the NodeID, which happens to correspond with the AlertStatusView.ActiveObject, between the two tables.

So when I select nodes which are n.Status = 2 (aka down), it retrieves the nodes name, IP, status, it also ties in the active alert for the node down

Links to help: SQL Joins

I hope it helps!