Move Computers to Group

dominique · 03-14-2008, 04:42 PM

Hello,

Is there a SQL way to move computers to a specific group with an SQL stament like..based on its IP?

IP is in the computer_display table

I found this one but working on group to group...

Quote:

USE eXpress
GO
UPDATE computer
SET group_id = (SELECT group_id from computer_group where name='New Computers')
FROM computer c
LEFT OUTER JOIN sessions s
ON c.computer_id = s.computer_id
WHERE s.session_id IS NOT NULL
AND c.group_id = (SELECT group_id from computer_group where name='Inactive Computers')

Thanks

dominique · 03-14-2008, 05:10 PM

Hello,

Quote:

USE eXpress
GO
UPDATE computer
SET group_id = (SELECT group_id from computer_group where name='xxx.xxx.xxx.xxx%')
FROM computer c
LEFT OUTER JOIN computer_display cd
ON c.computer_id = cd.computer_id
WHERE cd.ip_address LIKE 'xxx.xxx.xxx.xxx%')

This works for specifics subnets and groups already created.
Now questions:
- can I create a groupm automatically if a new subnet appears?
- can do this to run on any subnet for any ip_addresses?

To be able to run as a job which run faily to populate the groups with our workstations moving...

Thanks

dominique · 03-18-2008, 08:23 PM

Anybody if grouping the workstations by IP on DS?
Let me know how to do this?

Thanks

Nick · 03-18-2008, 10:52 PM

Dom,

Are you wanting to have an automated way to put systems into a specific group based on IP? If so the answer is, within the console no, if you want to get creative you could do some sql manipulation on the database such as what you've already demonstrated in the previous comments.

dominique · 03-19-2008, 12:31 AM

Hello Nick,

Yes I want to move the computer by a DS job to the correct group upon its IP. This job could run on a daily basis and do something similar t what we have on NS.

Thanks

dpeluso · 03-19-2008, 06:54 AM

I use the active directory sync here:
Deployment Server / Active Directory Synchronization | Altiris Juice

maybe you can extend that script ?

Mike · 03-19-2008, 11:08 AM

IP grouping can be tricky since it's not predictable as to which NIC DS will choose as NIC1 and use for the IP. All the same, this is a common practice for me. I like to create groups based on subnets and create a script like this one:

/*For Group A*/
UPDATE Computer SET group_id = 20
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.10.%' AND nic.[mask] = '255.255.255.0'
UPDATE Computer SET group_id = 20
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.11.%' AND nic.[mask] = '255.255.255.0'
/*For Group B*/
UPDATE Computer SET group_id = 21
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.20.%' AND nic.[mask] = '255.255.255.0'
/*For Group C*/
UPDATE Computer SET group_id = 22
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.30.%' AND nic.[mask] = '255.255.255.0'
UPDATE Computer SET group_id = 22
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.31.%' AND nic.[mask] = '255.255.255.0'
I put all of this in a .sql file and I run a call a bat file from a job if I need the groups to be straight for job execution. I do this primarily because I like to use folder heirarchy as a token.

jdean · 03-19-2008, 01:24 PM

And to add on to what Mike is saying, you'll have a lot of weird IPs if your users are remote through VPN.

And of course the IPs change when they bring their laptops in and are no longer remote/vpn.

Just a FYI.

Quote:

Originally Posted by Mike

IP grouping can be tricky since it's not predictable as to which NIC DS will choose as NIC1 and use for the IP. All the same, this is a common practice for me. I like to create groups based on subnets and create a script like this one:

/*For Group A*/
UPDATE Computer SET group_id = 20
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.10.%' AND nic.[mask] = '255.255.255.0'
UPDATE Computer SET group_id = 20
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.11.%' AND nic.[mask] = '255.255.255.0'
/*For Group B*/
UPDATE Computer SET group_id = 21
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.20.%' AND nic.[mask] = '255.255.255.0'
/*For Group C*/
UPDATE Computer SET group_id = 22
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.30.%' AND nic.[mask] = '255.255.255.0'
UPDATE Computer SET group_id = 22
FROM Computer c INNER JOIN nic_interface nic on c.computer_id = nic.[id]
WHERE nic.ip LIKE '10.10.31.%' AND nic.[mask] = '255.255.255.0'
I put all of this in a .sql file and I run a call a bat file from a job if I need the groups to be straight for job execution. I do this primarily because I like to use folder heirarchy as a token.

dominique · 03-19-2008, 01:33 PM

Thanks All I will check with my boss which option will be the best as I am not sure AD is relevant as not clean, so IP will be the way with a big bucket for OTHERS like VPNs...

dominique · 03-19-2008, 07:53 PM

yes double NICs and Netmotion are giving strange results...
- Double NIC whatever NIC reply it goes... so there is no consitency, is it possible to request the response from a specific NIC cars(slot, manufacturer, speed, etc...)?
- NetMotion it is the server which is replying !!!

all workstation have two IPs one virtual from the server and one physical and it is most of the time the virtual which is replying, so the server before the workstation itself...

Mike,

Why did you incluide the nic.[mask] in the selection criteria?

Thanks

Mike · 03-24-2008, 08:52 PM

I included the mask in the selection criteria because DS doesn't record your subnet. This is the closest I could get with my limited sql powers to identifying the subnet based on data in the express db.

I'm sure someone who is good at vbscript could write something that figures out the subnet based on data in the db and make something more efficient.

dominique · 03-24-2008, 09:11 PM

I am using your SQL it is great as it shows the sites wrongly affected fast. For me it is excellent. I have moved 150 machines from one server to another because of your SQL itis really good.

I was asking the subnet question as I have some desktops on 255.255.255.0 and some on 255.255.0.0 so they were not picked but I will check why we have two masks... it is weird...

Thanks,

stewfo · 05-12-2008, 04:45 AM

Hey Dom, can you explain how you have managed to move computers form one server to another.
The reason i ask is that i have 2 DS's and have to manually move computers from 1 ds to the other. Would like to be able to run a sql query to do this for me.

Cheers

Stewfo.

dominique · 05-12-2008, 01:24 PM

Hello,

The steps I am doing are:
- Create Groups on the actual server with the IP grouping SQL provided by Mike,
- Change the settings of the groups with a job from one of these KBs:
- KB38613
- KB17607
- KB12028

)Changing the registry or pushing a AClient.inp file)

Thanks

deemacgee · 02-02-2009, 12:38 AM

I know this thread is kinda old and stuff, but I ended up writing a SQL query to automatically move PCs around the Console, based on Subnet details. Have been using it for a month or two now and, so far, no adverse effects.

... maybe someone else will find it useful, maybe not, but here it is:

Clickety Click

Nick · 02-02-2009, 08:24 AM

Thanks for sharing dee! Nice title for the blog too!