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

SWQL Case Sensitive Query

I know in SQL Server you can change collation in your where clause to perform a case sensitive search on a string. Does SWQL have such a function?

I looked in the Wiki on GitHub and didn't see a function like that, but I know there are many functions that have been added to SWQL that don't appear on that Wiki (e.g. ChangeTimeZone, ToServerLocalFromUtc, and Grouping). I'm writing a script and, while I can do that in the script's code, it would be far easier to perform it in the query.

Thanks for any help you can offer.

Find more posts tagged with

query

SWQL

orion sdk

Accepted answers

All comments

patrick.hubbard

That's a great question. I've been reading and don't see an equivalent to SQL's per query:

<span class="hljs-keyword">COLLATE</span> SQL_Latin1_General_CP1_CI_AS

That may be by design, as SWQL is automatically creating joins for you using the cached object data in Orion, instead of hitting the DB for each UI update. (Everything in Orion, including the website, is based on SWIS).

Are you looking to change how joins and selects work, for example case insensitive "bob" == "BOB", or are you looking to change the result sort order in a specific way?

mrxinu

Hey there!

Are you looking to see if two strings have an identical case, or that they're equivalent despite the case? If it's the former, you may be stuck converting this query to SQL. If it's the latter, convert both strings to lowercase with ToLower(string) and then compare them.

xtraspecialj

So we are trying to script our captions to be certain cases based on the node type. So Windows nodes will be all upper case, Linux/Unix nodes will start with Upper case, ESX Hosts will be all upper case and include the domain (so FQDN), VIP's will be all lowercase, and etc... Obviously I can just query the data and do it in script, but it'd be a good bit easier to just do it in SWQL (for example, for all Windows nodes I would want to query only nodes whose Vendor is 'Windows' and where the caption contains any lowercase letters. Or for the Linux version I'd query nodes whose vendor is 'net-snmp' and whose first letter does not contain an uppercase and any other letter contains an upper case.)

I ended up doing this all in script because, for the level of sophistication we need, it actually makes more sense in hindsight.

Still, it'd be nice if they put a function in SWQL to allow for matching on case sensitivity for simpler use cases than this.

xtraspecialj

Thanks for the reply. See my reply to mrxinu in this thread to see my use case. But I ended up doing everything I needed in my PowerShell script, which in hindsight was more appropriate for my use case.

mrxinu

For sure, and that's how the other functions made their way into SWQL. Open a feature request and we shall vote it into existence!