Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

I need help identifying the error in this script. greatly appreciated if you could.

tmotofga

SELECT

ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0) AS Month,

Nodes.Caption AS NodeName,

Nodes.DetailsUrl AS NodesDetailsUrl,

Application.Name AS ApplicationName,

Application.DetailsUrl AS ApplicationDetailsUrl,

Orion.APM.ApplicationCustomProperties.Apps_Priority AS Priority,

SUM(ApplicationStatus.PercentAvailability * ApplicationStatus.RecordCount) / SUM(ApplicationStatus.RecordCount) AS PercentAvailability

FROM Orion.APM.ApplicationStatus ApplicationStatus, Orion.APM.ApplicationCustomProperties ApplicationPriority

INNER JOIN Orion.APM.Application Application ON Application.ApplicationID = ApplicationStatus.ApplicationID

INNER JOIN Orion.Nodes Nodes ON Nodes.NodeID = Application.NodeID

INNER JOIN Orion.APM.ApplicationCustomProperties Priority ON Priority.ApplicationId = Application.ApplicationID

WHERE ApplicationStatus.TimeStamp >= ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0)) AND

ApplicationStatus.TimeStamp < ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()), 0)) AND

Orion.APM.ApplicationCustomProperties.Apps_Priority = 'Critical'

GROUP BY Application.ApplicationID, Application.Name, Nodes.Caption, NodesDetailsUrl, ApplicationDetailsUrl, Orion.APM.ApplicationCustomProperties.Apps_Priority

ORDER BY Nodes.Caption, Application.Name

Find more posts tagged with

Accepted answers

chad.every

What is your goal with the datetime? It looks like you're trying to find "Last Month"? Or are you trying to look for something different?

The query can be simplified to this.

SELECT
    ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0) AS Month,
    ApplicationStatus.Application.Node.Caption AS NodeName,
    ApplicationStatus.Application.Node.DetailsUrl AS NodesDetailsUrl,
    ApplicationStatus.Application.Name AS ApplicationName,
    ApplicationStatus.Application.DetailsUrl AS ApplicationDetailsUrl,
    ApplicationStatus.Application.CustomProperties.Apps_Priority AS Priority,
    SUM(ApplicationStatus.PercentAvailability * ApplicationStatus.RecordCount) / SUM(ApplicationStatus.RecordCount) AS PercentAvailability

FROM Orion.APM.ApplicationStatus ApplicationStatus

WHERE ApplicationStatus.TimeStamp >= ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0)) 
    AND ApplicationStatus.TimeStamp < ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()), 0)) 
    AND ApplicationStatus.Application.CustomProperties.Apps_Priority = 'Critical'

GROUP BY 
    ApplicationStatus.Application.ApplicationID, 
    ApplicationStatus.Application.Name, 
    ApplicationStatus.Application.Node.Caption, 
    ApplicationStatus.Application.Node.DetailsUrl, 
    ApplicationStatus.Application.DetailsUrl,
    ApplicationStatus.Application.CustomProperties.Apps_Priority

ORDER BY ApplicationStatus.Application.Node.Caption, ApplicationStatus.Application.Name

The reason this query can be simplified is because SWQL already has native JOINS built in for you.

You alias the table: ApplicationStatus
This references the namespace Orion.APM.ApplicationStatus
This namespace as a JOIN already associated to it for Application (Joined to Orion.APM.Application)
You can reference this automatically through your original alias and automatically joined alias

As you look in other tables, you'll see other JOINS automatically created. This is how we can traverse to the Orion.Nodes table because Orion.APM.Application has a JOIN for Orion.Nodes.

ApplicationStatus --> Application --> Node

tmotofga

You're a gem. Thanks so much. I'll keep that in mind next time.

All comments

kpina

Hi @tmotofga - I'm moving this to the SolarWinds Observability Self-Hosted group's forum so it'll get more eyes on it/a faster response.

tmotofga

Thanks much. The script breaks here, at bottom before group statement.....

AND

Orion.APM.ApplicationCustomProperties.Apps Priority = 'Critical'

chad.every

What is your goal with the datetime? It looks like you're trying to find "Last Month"? Or are you trying to look for something different?

The query can be simplified to this.

SELECT
    ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0) AS Month,
    ApplicationStatus.Application.Node.Caption AS NodeName,
    ApplicationStatus.Application.Node.DetailsUrl AS NodesDetailsUrl,
    ApplicationStatus.Application.Name AS ApplicationName,
    ApplicationStatus.Application.DetailsUrl AS ApplicationDetailsUrl,
    ApplicationStatus.Application.CustomProperties.Apps_Priority AS Priority,
    SUM(ApplicationStatus.PercentAvailability * ApplicationStatus.RecordCount) / SUM(ApplicationStatus.RecordCount) AS PercentAvailability

FROM Orion.APM.ApplicationStatus ApplicationStatus

WHERE ApplicationStatus.TimeStamp >= ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0)) 
    AND ApplicationStatus.TimeStamp < ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()), 0)) 
    AND ApplicationStatus.Application.CustomProperties.Apps_Priority = 'Critical'

GROUP BY 
    ApplicationStatus.Application.ApplicationID, 
    ApplicationStatus.Application.Name, 
    ApplicationStatus.Application.Node.Caption, 
    ApplicationStatus.Application.Node.DetailsUrl, 
    ApplicationStatus.Application.DetailsUrl,
    ApplicationStatus.Application.CustomProperties.Apps_Priority

ORDER BY ApplicationStatus.Application.Node.Caption, ApplicationStatus.Application.Name

The reason this query can be simplified is because SWQL already has native JOINS built in for you.

You alias the table: ApplicationStatus
This references the namespace Orion.APM.ApplicationStatus
This namespace as a JOIN already associated to it for Application (Joined to Orion.APM.Application)
You can reference this automatically through your original alias and automatically joined alias

As you look in other tables, you'll see other JOINS automatically created. This is how we can traverse to the Orion.Nodes table because Orion.APM.Application has a JOIN for Orion.Nodes.

ApplicationStatus --> Application --> Node

tmotofga

You're a gem. Thanks so much. I'll keep that in mind next time.

WindsOfNishat

Hello @chad.every

Is there any way to learn and understand SWQL? I want to learn it but don't have any prior experience with scripting. However, to work effectively with SolarWinds, I need to learn SWQL as soon as possible.

Could you please suggest a good approach, documentation, or any other study materials to learn SWQL from scratch?

chad.every

These are my goto links for that:

SWQL Walkthrough

Intro to API, SDK & SWQL

Home · solarwinds/OrionSDK Wiki · GitHub

weston.hood

Here are some tips that have helped me the most. One, check out the wiki that Chad shared. Just know that it’s basically just SQL with a few limitations and subtle differences that you'll find in the wiki [functions]. There's also visual pros to using it over sql, plus it obeys account limitations. I learned SQL from codecadamy, you can learn a surprising amount for free, or at least you used to be able to 8 years ago. Worst case, most companies will expense this as training. It’s a really good interactive code training where you get to write some scripts to learn the concepts. Once you learn the basics of SQL, SWQL is a breeze.

You can also leverage features in the platform that write SWQL for you. If you build an alert, it is actually generating a swql script behind the scenes. Click on the carrot to expose the generated SWQL. You can use this as a tool to help find what tables things are held in. I would also download Swql Studio, from the github. That tool also has a search so you can search for columns, tables, verbs, etc.

I would eventually import some modern dashboards. Top downloaded dashboards here. Reason being, you can quickly import some useful dashboards into your system so that you can use. But secondly, you can go into edit mode on each of these widgets and view the SWQL code so that you can learn the syntax used for things like hyperlinking, using icons, progress bars, etc. etc. Importing one dashboard might give you 50 different queries from all the resources it has.

WindsOfNishat

Thanks for your response, I will check the shred doc.