Deployment and Imaging Group

We currently have a RAW SQL filter that looks like the following that we use for patch management that gives us machines ending in 1,2,3 and so on:

SELECT
   [vri2_Computer].[Guid],
   [vri2_Computer].[Name]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
WHERE ([vri2_Computer].[Name] LIKE '%1' OR  [vri2_Computer].[Name] LIKE '%2' OR  [vri2_Computer].[Name] LIKE '%3')

This gives us all machines however we would like to change this to be country based and Windows 7 only so all machines that are W7LONDON that end in 1,2,3 and W7FRANCE 1,2,3 and so on in seperate filters.  Does that make sense?

What is the best way to do this please?

Thanks.

You could join the table Inv_AeX_AC_Identification to get the operating system and you could just add the country / location name in the like part.

For example like this:

SELECT
   [vri2_Computer].[Guid]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
LEFT JOIN Inv_AeX_AC_Identification ident on ident._ResourceGuid = [vri2_Computer].[Guid]
WHERE ([vri2_Computer].[Name] LIKE '%LONDON%1' OR  [vri2_Computer].[Name] LIKE '%LONDON%2' OR  [vri2_Computer].[Name] LIKE '%LONDON%3')
and ident.[OS Name] like 'Windows 7%'

 

i agree that would be the best way to do it... 

or you could create filters based off of country...

then use the same 1,2, 3 logic but add the country filter to the where statement. Either way will workb ut I think Stefan method would be easiest

Doesnt seem to be working - do I need to do anything else when creating a new filter?  Define the DB or any parameters?  Thanks.

If your computers are for example W7LONDON0001, W7LONDON0002, W7LONDON0003 and so that should just work if you take copy past from the sql query I posted. Do you get any error message when you save the filter?

If you want to do it based off name only and not check any of the inventory tables you can use regular expressions. Either way I would use vComputer instead of joining tables unless you just enjoy SQL ;)

SELECT co.[Guid] ,co.[Name] FROM [vComputer] co WHERE (co.[Name] LIKE '[wW][7]LONDON%[1-3]') Method #2 is: SELECT co.[Guid] ,co.[Name] FROM [vComputer] co WHERE (co.[Name] LIKE '%LONDON%[1-3]' AND co.[OS Name] LIKE 'Windows 7%')

With a little bit of tweaking I have this working now - many thanks for ALL your replies.