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

Fix substring for the custom query resource

Within SWQL studio we can use substrings without an issue but if you copy that same query to a custom query resource on the web console it reports back a SWIS error. I'd like to clean up the presentation of some of the data I'm getting back from my custom reports.

Find more posts tagged with

Status: None

Comments

wesleykparker

This is an old feature request, but I ran into the trouble today and saw no responses with a search that addressed it. I've found that you can't use the SUBSTRING feature in a "Custom Query" resource unless you use an "ORDER BY" statement. If there is no "ORDER BY" then the query will not run even if you have commented out the SUBSTRING command with "--".

Ex: (This one works)

select

CASE WHEN CharIndex('.',n.caption)-1 < 5 THEN n.Caption ELSE SUBSTRING(n.caption,1,charindex('.',n.caption)-1) END as [Device]

FROM Orion.Nodes n

Where status=2

ORDER BY Status

(This one does not work)

select

CASE WHEN CharIndex('.',n.caption)-1 < 5 THEN n.Caption ELSE SUBSTRING(n.caption,1,charindex('.',n.caption)-1) END as [Device]

FROM Orion.Nodes n

Where status=2

For anyone looking to clean up their FQDN the "case" statement above looks for a "." in the caption and cuts everything off after that. For captions that have no period or the period is in the first 5 spaces (i.e. an IP address) the output will be the entire Caption.

mesverrum

Yeah, since then I had also come across the "order by" solution which also deals with a few other kinds of problems you can sometimes get with the custom query resource. When in doubt, make sure to specify and order by.