titan90

I am looking to use a query that will divide workstations into one of three automatic collections based upon the letter the computer name begins with. My goal is to use these collections for patching roll outs.

For example:
Collection 1 = A-H
Collection 2 = I-O
Collection 3 = P-Z

These need to be Workstations only - no servers.

With my basic SQL knowledge I am grabbing the computer name and doing > or = (letter) but am curious if there is a better way.

Thanks.

Titan90

titan90

well here is what I came up with as a report:

SELECT [vResourceEx].[Name] AS 'Name', [Inv_AeX_AC_Identification].[OS Name] AS 'OS Name', [Inv_AeX_AC_Identification].[OS Type] AS 'OS Type', [Inv_AeX_AC_Identification].[System Type] AS 'System Type' FROM [vResourceEx] INNER JOIN [Inv_AeX_AC_Identification] ON [vResourceEx].[Guid] = [Inv_AeX_AC_Identification].[ResourceGuid] WHERE [vResourceEx].[IsManaged] = 1 AND ([vResourceEx].[Name] >= 'rl' AND [vResourceEx].[Name] <= 'Z') AND [Inv_AeX_AC_Identification].[OS Version] = '5.1' ORDER BY [vResourceEx].[Name] ASC

I will now focus on turning this into a collection query - but it's a start.

titan90

jperry933

I did not change anything in your collection except for the section highlighted below. For each collection, change the range '[a-h]%' to the range you need.

SELECT [vResourceEx].[Name] AS 'Name',
[Inv_AeX_AC_Identification].[OS Name] AS 'OS Name',
[Inv_AeX_AC_Identification].[OS Type] AS 'OS Type',
[Inv_AeX_AC_Identification].[System Type] AS 'System Type'
FROM [vResourceEx] INNER JOIN [Inv_AeX_AC_Identification]
ON [vResourceEx].[Guid] = [Inv_AeX_AC_Identification].[_ResourceGuid]
WHERE [vResourceEx].[IsManaged] = 1
AND [vResourceEx].[Name] like '[a-h]%'
AND [Inv_AeX_AC_Identification].[OS Version] = '5.1'
ORDER BY [vResourceEx].[Name] ASC

__________________

jperry933

oh, forgot you want it as a collection:

SELECT [vResourceEx].[Guid]
FROM [vResourceEx] INNER JOIN [Inv_AeX_AC_Identification]
ON [vResourceEx].[Guid] = [Inv_AeX_AC_Identification].[_ResourceGuid]
WHERE [vResourceEx].[IsManaged] = 1
AND [vResourceEx].[Name] like '[a-h]%'
AND [Inv_AeX_AC_Identification].[OS Version] = '5.1'
ORDER BY [vResourceEx].[Name] ASC

__________________

titan90

I got and error when trying to get this query to run as a collection - but pulled the "Order by" statement out and it worked like a charm.

Thanks so much for the help.


titan90