When creating a custom Query and using the search function is it possible to ignore capital letters

dodo123

I'm using this in the where statement feel like there should be another variable?

WHERE s.Polling.Node.Caption LIKE '%${SEARCH_STRING}%' OR s.Name LIKE '%${SEARCH_STRING}%'

Find more posts tagged with

swql query

custom query widget

SWQL

Accepted answers

sum_giais

So thinking about this more, you might be able just wrap the comparison fields in a ToLower() so you're forcing them to be the same.

Something like this may work:

WHERE ToLower(s.Polling.Node.Caption) LIKE ToLower('%${SEARCH_STRING}%') OR ToLower(s.Name) LIKE ToLower('%${SEARCH_STRING}%')

All comments

sum_giais

That's strange I didn't think the LIKE keyword was case sensitive... mind sharing the query? I'll see if I get the same behaviour and mess with it a bit.

sum_giais

So thinking about this more, you might be able just wrap the comparison fields in a ToLower() so you're forcing them to be the same.

Something like this may work:

WHERE ToLower(s.Polling.Node.Caption) LIKE ToLower('%${SEARCH_STRING}%') OR ToLower(s.Name) LIKE ToLower('%${SEARCH_STRING}%')

shuth

I've never seen the search box be case-sensitive like that. I think it depends on your SQL collation as SWQL queries are converted to SQL.

Unless I'm misreading the question and you want the search to be case-sensitive?

dodo123

So this was the search query

SELECT
s.Polling.Node.Caption as [Node Name],
'/Orion/images/StatusIcons/small-' + ToString(s.Polling.Node.StatusIcon) AS [_IconFor_Node Name],
s.Polling.Node.DetailsUrl AS [_LinkFor_Node Name],
s.Name,
s.InstallDate as [Install Date],
s.Publisher,
s.Version
FROM Orion.AssetInventory.Software s
WHERE s.Polling.Node.Caption LIKE '%${SEARCH_STRING}%' OR s.Name LIKE '%${SEARCH_STRING}%'

dodo123

perfect that did the trick!

SELECT
s.Polling.Node.Caption as [Node Name],
'/Orion/images/StatusIcons/small-' + ToString(s.Polling.Node.StatusIcon) AS [_IconFor_Node Name],
s.Polling.Node.DetailsUrl AS [_LinkFor_Node Name],
s.Name,
s.InstallDate as [Install Date],
s.Publisher,
s.Version
FROM Orion.AssetInventory.Software s
WHERE ToLower(s.Polling.Node.Caption) LIKE ToLower('%${SEARCH_STRING}%') OR ToLower(s.Name) LIKE ToLower('%${SEARCH_STRING}%')

Seashore

The deep down technical answer to why it behaves like this here and only on this table:

The SQL table [AssetInventory_Software] containing this info is NOT case sensitive. So I ran the query in SWQL studio and there it's case sensitive. In SWQL studio you can add "With querystats" to see the actual SQL query behind this:

SELECT TOP 1000 NodeID, Name, Version, Publisher, InstallDate FROM Orion.AssetInventory.Software Where name like '%Agent%'

with QUERYSTATS

That shows that the actual SQL query is:

SET DATEFIRST 7; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (1000) [T1].[NodeID] AS C1, [T1].[Name] AS C2, [T1].[Version] AS C3, [T1].[Publisher] AS C4, [T1].[InstallDate] AS C5 FROM dbo.AssetInventory_SoftwareView AS T1 WHERE [T1].[Name] LIKE '%agent%'

We now find that Orion queries the SQL view called [AssetInventory_SoftwareView] and not the table directly.

So, let's check how the view is created:

CREATE VIEW [dbo].[AssetInventory_SoftwareView]
AS
SELECT
[NodeID],
[Name] collate SQL_Latin1_General_CP1_CS_AS AS [Name],
[Publisher],
MAX(CASE WHEN [Version] = '0' THEN '' ELSE [Version] END) AS [Version],
MAX([InstallDate]) AS [InstallDate]
FROM [dbo].[AssetInventory_Software] a
GROUP BY
[NodeID],
[Name] collate SQL_Latin1_General_CP1_CS_AS,
[Publisher]
GO

The column [name] is for some reason collated as CS=Case Sensitive.

And that is why we get this behavior.