Mike

Hi,
I have an idea that; should we be able to sort it out, will make a really neat kb article.

It occured to me that the current method for resolving package servers in DS (bluewillow's PSLookup, or the Altiris framework KB) both require the transfering of "lookup files" to the clients and then some utility is run to find the subnet, extract a PS name from the file and make a variable out of it.

Why can't we do this server side? It would simplify this process SO much!
Here's what I've got, I created a DB in my sql server named Lookup. In there is a table of PS names, subnets and some sorting / primary key info.

I want to be able to run a query that says, "if my subnet is A then by Joining to the Lookup.Lookup table I can deem that my package server is PS-A" and all of this can be resolved in a custom token that you could use over and over again from any job without the extra scripting that comes from the current route.

Here's the trouble. The Aclient doesn't report "subnet" to the DS database! So the query will need to be able to determine the subnet based on the IP Address and the subnet mask! Ooh... suddently things got more complicated...

Your thoughts?

__________________
______________________
Mike Ainsworth

Nick

Sounds like a good idea.

Let me check the express database and see if anything comes to mind. There are a couple of SQL guru's that owe me some time from another project I paid them for. Might be able to get them to come up with something for us.

__________________
Scire potentia est (knowledge is power)

Mike

This isn't exactly what I was going for but it's close:

You could sort your computers in your DS console into groups that follow a Site model and name the groups after the local distribution server.

For example, Site A has a server named vader (for Nick). I would actually create a group named Vader and put the computers that should use this distro under that group. Then I could use the following custom token inplace of a server name in any UNC path for deploying software, capturing PCT packages, etc:

%#*"select cg.[Name] from computer_group cg inner join computer c on cg.[group_id] = c.[group_id] where c.[computer_id] = %ID%"%

It's really long and crazy looking, but I've used this quite successfully recently.

So there ya go.

M

__________________
______________________
Mike Ainsworth

Nick

I like the process on this Mike. Using this idea you could also leverage the ability to run a scheduled task to place managed systems into the groups using the predefined criteria from one of your other projects. I think we should get some screenshots of this and make a KBA for it.

UPDATE Computer
SET Group_Id = group number goes here
FROM Computer WHERE Name like 'sitecode%'

Then to get the needed info for above....

and how do I easily obtain the group id?

'what's my group id?
'vbscript
msgbox "Your Group Name: %#*"select cg.[Name] from computer_group cg inner join computer c on cg.[group_id] = c.[group_id] where c.[computer_id] = %ID%"%"
msgbox "Your Group ID: %#*"select cg.[group_id] from computer_group cg inner join computer c on cg.[group_id] = c.[group_id] where c.[computer_id] = %ID%"%"

__________________
Scire potentia est (knowledge is power)

Mike

I was getting ready to write the whitepaper and was looking around to see what else is out there.
Check out Altiris KB # 17101.
I like this one. Instead of using the name of the group as the variable, they use a SQL script to update the "SITE" property in DS to a value of the name of the server.
Then you simply use %SITE% which is allot easier looking on the eyes then my examples

The previous method is still valid and for some it may be preferred. You would still use the previousely mentioned (by Nick) sql script to update the group memberships in your server. You could also set the group memberships by AD OU, Subnet, Computer Name or whatever.

__________________
______________________
Mike Ainsworth

dclapp

I thought that it would be useful to move computer objects in DS into folders based on a criteria. Here is an example of that:

UPDATE [express].[dbo].[computer] SET [group_id]='1' WHERE [computer_id] IN ((SELECT [Computer].[computer_id] FROM [computer] INNER JOIN [nic_interface] ON [computer].[computer_id] = [nic_interface].[id] WHERE [nic_interface].[nic_id]='0' AND [nic_interface].[ip] LIKE '192.168.18%'))

UPDATE [express].[dbo].[computer] SET [group_id]='2' WHERE [computer_id] IN ((SELECT [Computer].[computer_id] FROM [computer] INNER JOIN [nic_interface] ON [computer].[computer_id] = [nic_interface].[id] WHERE [nic_interface].[nic_id]='0' AND [nic_interface].[ip] LIKE '192.168.1.%'))

In this case I used the IP address to put computers into folder based on subnets.

__________________
"If there is a doubt -there is no doubt."

David Clapp
MCP,CCA,ACE
Inacom Information Systems, Inc.

Nick

Good idea David. I've done it a bit different in the past by using conditions to seperate out the various subnets.

Thanks for showing an example to do it this way!

__________________
Scire potentia est (knowledge is power)

dferrett

Problem I see with using NIC info from DS, is with laptops that have wireless NICS built in. Quite often these are the first NIC and DS reports their IP address as 0.0.0.0, so no subnet info available. YOu would either have to go to each laptop and set the 'real' NIC to number 1, or have the SQL query check for a valid address, and if not check NIC 2, 3 and so on.

dclapp

Laptops with wireless network cards always seem to present a problem. In the mean time, I have worked with a SQL friend of mine to develop a function to address this. We are able to filter out based on the first three digits in the ip address.

First, we create a user defined function in SQL called GetGroupbyIP

----------------------------------------------
USE [dev_eXpress] --Put your daterbase name in here
GO
/****** Object: UserDefinedFunction [dbo].[GetGroupbyIP] Script Date: 05/24/2007 15:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[GetGroupbyIP]
(
@computer_id int
)
returns int
as
begin
declare @RetVal int

set @RetVal =
(
select [group] =
case substring(ip,1,6)
when '172.20' then '110' --Milwaukee
when '172.21' then '111' --Madison
when '172.22' then '112' --Waukesha
when '172.23' then '108' --Rockford
else '113' --Unassigned
end
from nic_interface ni
where substring(ip,1,3) = '172'
and ni.id = @computer_id
)

if @RetVal is null --If it can't find the computer_id passed in, it will still return 113 for Unassigned
begin
set @RetVal = 113
end

return @RetVal
end
----------------------------------------------


The we call the function with an UPDATE sql statement.
----------------------------------------------
update computer
set group_id = dbo.GetGroupbyIP(computer_id)
----------------------------------------------

__________________
"If there is a doubt -there is no doubt."

David Clapp
MCP,CCA,ACE
Inacom Information Systems, Inc.