0

I am attempting to write a query for an Altiris report. This query is looking for machine information. The query works fine, however the problem I am running into is with my parameters. I have set up multiple parameters within Altiris to allow me to filter and search through the report for multiple fields. Then, in my query, I add those parameters into the WHERE statements.

All of the parameters were working fine, until I added Make and Model parameters. We have quite a few machines that do not have information populated into these fields. So when I add in the WHERE xxxx LIKE N'%Make%', I lose about 500 machines based on it now only looking for machines with something in that field. I tried to fix this by adding lines like the following:

Where ((xxxx LIKE N'%Make%' OR xxxx is null))

This kind of worked, in that now the report shows all machines... But if I enter "HP" into the Make parameter field and then rerun the report... it shows all HP machines like I want, but also all of the null machines as well.

How can I rewrite my where statements so that they do not exclude machines in the report, and allow me to filter by all HP machines, without showing null values as well?

Hope this made sense, and thank you

In this snip of code, the last two lines make me lose about 500 machines in my total machine count of the report. It is omitting all machines that have null values.

WHERE
  (dbo.OS_Version.[OS Name] LIKE N'%OSName%') AND 
  (dbo.OS_Version.[OS Version] LIKE N'%Build%') AND 
  (dbo.OS_Version.Name LIKE N'%Name%') AND 
  (dbo.Inv_AeX_AC_Identification.[Hardware Serial Number] LIKE N'%Serial%') AND
  (dbo.vHWComputerSystem.Manufacturer LIKE N'%Make%') AND
  (dbo.vHWComputerSystem.Model LIKE N'%Model%')

This is how I tried to fix it, and now I get all 20,000 machines. But my make/model fields report on null fields as well.

WHERE
  (dbo.OS_Version.[OS Name] LIKE N'%OSName%') AND
  (dbo.OS_Version.[OS Version] LIKE N'%Build%') AND 
  (dbo.OS_Version.Name LIKE N'%Name%') AND 
  (dbo.Inv_AeX_AC_Identification.[Hardware Serial Number] LIKE N'%Serial%') AND
  ((dbo.vHWComputerSystem.Manufacturer LIKE N'%Make%') OR (dbo.vHWComputerSystem.Manufacturer is null)) AND
  ((dbo.vHWComputerSystem.Model LIKE N'%Model%') OR (dbo.vHWComputerSystem.Model is null))
Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
Jh8212
  • 3
  • 1
  • Is the reporting system replacing these strings `OSName` etc? – Blorgbeard Apr 24 '19 at 16:03
  • what is LIKE N'%OSName%' ? .. i never see this – ScaisEdge Apr 24 '19 at 16:05
  • So, I am utilizing Altiris to create custom reports. Within the Altiris report creation, I created a few parameters. One for every WHERE statement. So when you see "LIKE N'%OSName%", I actually have a parameter created called OSName. It automatically replaced OSName with whatever I have in that parameter field. – Jh8212 Apr 25 '19 at 16:09

3 Answers3

1

I'm guess that if you don't enter a value for a parameter, it's coming through as an empty string, and of course, every varchar is LIKE '%%'.

I'm not sure what RDBMS this is, but if the ISNULL function is available, try this:

where ((ISNULL(xxxx,'') LIKE N'%Make%')

This replaces nulls with the empty string before doing the LIKE comparison.

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
1

I think you want something like this:

  (cs.Manufacturer LIKE N'%@Make%' OR @Make = '') AND
  (cs.Model LIKE N'%@Model%' OR @Model = '')

I am using = '' rather than IS NULL because you are clearly not passing in the parameters as NULL values (the LIKE wouldn't work).

This does not provide a method for filtering to get only the NULL values, because you are using the "special value" for the parameter to mean "don't apply a filter here".

Note that cs is intended as a table alias. I also strongly recommend that you use table aliases so your queries are easier to write and to read.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Question on this. In my report, how I have it currently, when I plug in "HP" into the "Make" parameter, and then run this query, it reports back all machines with a Manufacturer of HP. But it also returns all null machines as well. Would this fix that? Because originally before I added on the OR statement, my entire report just cut out any machine that didn't have the Manufacturer field populated. – Jh8212 Apr 25 '19 at 16:20
  • @Jh8212 . . . If `@Make` is `'HP'`, then this query only returns rows with `'HP'`. – Gordon Linoff Apr 26 '19 at 01:28
0

I think you're looking for something like WHERE ISNULL(Model, '') LIKE '%Model%'. However, you should replace '%Model%' with a variable. The above example would literally match the word 'Model'

DECLARE @Model NVARCHAR(100) = 'T-800'
...
WHERE ISNULL(Model, '') LIKE '%' + @Model + '%'

^ This would not include rows with NULL Model values

Superjerk
  • 1
  • 2