SQL Query for MAC Address

[Bill Carter]

Can anyone tell me how to create a query, either in SMS (ad hoc or other
wise), or in SQL that will dump the MAC (Nic card) address into a text file.
I want to use the wake on lan feature on our new pc's but this require you
to specify the mac address. I can use a text file to wake a number of
machines at the same time. We have rolled out several thousand pc's with
this feature and it would be a great time saver to produce these file
automatically. The information is in the SMS database but I do not know how
to extract it into a text file. I do not want to query on a specific
address but want to dump the addresses of several hundred machines into a
text file. I have a little Sql experience but need help in performing this
operation. Thanks in advance.

--
Bill Carter
car...@home.com

[Andre Guerrero]

you can write a query which will extract the mac address from your sms
db. you obviously have to know the field names in your sms db, and then
it's a rather simple query:

select "mac_address_fieldname" from "sms_dbname"

Andre

[Bob Pfeiff]

You could create a view in SQL Server that produces a resultset with the MAC
address and use bcp to export to a text file from the view, or use the isql
command line utility to redirect the results of a query of the MAC addresses
to a file.

Examples:

create view MAC_Address
as
select
MAC
from
someSMStable
go

Then run this from a command line or batch file:

bcp smsdatabase..MAC_Address out c:\MAC_Address.txt /c /Sservername
/Uloginid /Ppassword

Or run this from a command line or batch file (the file with the output will
have column headers included):

isql /Sservername /Ulogin /Ppassword /dSMSdatabase /Q"select MAC from
someSMStable" /oc:\MAC_Address.txt

There is more info on bcp and isql in SQL Server books online.
--
Bob Pfeiff
MCSD, MCT, SQL Server MVP

[George]

If a task can be accomplished by using the SMS Administrator Tool natively,
let's not make it any harder than it has to be. You can accomplish what
you're wanting by following the steps outlined below. This will also
associate the Mac address to a computer name.

1. From the Sites, select Define Query Results Format.. from the File menu.
2. Click the New button.
3. Type "Computers by Mac Address" in the name field without the quotes.
4. From this point you can add the computer properties that you want to
see. To make it simple, scroll down until you get to the the Identification
Group and highlight Name and click on the Add To Format button and also
select NetcardID and click on the Add To Format button and click the OK
button.
5. This should bring you back to the Define Query Result Formats dialog
box, click OK again.
6. From the Sites window, select Execute Query.
7. In the Query drop down list box, select All Personal Computers.
8. In the Query Result Format drop down list box, select the Computers by
Mac Address (or whatever name you called in step #3).
9. Click the OK button.
10. You will now have two columns, Computer Name and Mac Address.
11. From this point, click Edit - Select All. This will highlight all the
data.
12. Click Edit again and select Copy Table.
13. Last but not least you can paste this information into Microsoft Excel
and it will paste into the columns very nicely.

Bill Carter wrote in message ...

[QuakeII]

Hi,

I'm no SQL expert either, but can't you perform a query for the first six
chars of the MAC address, probably all the same cards (I mean the
Manufacturers code) I think only the HP's have this feature right now.

Just a thought...

[Ian Turek <Gravity Square Inc.>]

Sure. All of the below is SMS 2.0 stuff.

In SQL you can use _ and % wildcard characters and like keyword: % stands
for any number of characters (including 0) and _ stands for a single
character

For instance to find all MAC addresses that start with 00:C0:4F (standing
for 3Com)

you can use:

select * from Netcard_DATA where MACAddress0 like "00:C0:4F:%"

similarly you might want to create a query or a collection in AdminUI you
will have to query for all system resource attributes where a simple value

Attribute Class - Network Adapter
Attribute - MAC Address
is like
00:C0:4F%

which in WQL (Wbem Query Language) will look like:

select SMS_R_System.* from SMS_R_System inner join
SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceID =
SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER.MACAddress like
"00:C0:4F:%"

Isn't this simple.

Good Luck

Ian Turek
Systems Scalability Engineer
Gravity Square Inc.
www.gravitysquare.com

QuakeII wrote in message <76qght$rau$1...@news.worldonline.nl>...