Querying the SQL database
I want to query the SQL database directly on the SQL server since I have thin client terminals that cannot become part of the site and hence cannot run reports on them.
I can find entries for all the devices in the dbo.CollectionMembers table. Which table would I find the IP address and Mac add for these devices?
Any help appreciated.
Thanks
November 17th, 2009 12:27pm
I don't get the problem ... on one hand you wrote "client terminals that cannot become part of the site" (does that mean you cannot install the ConfigMgr client on them?) and on the other hand you want to "find the IP address and Mac add for these devices"? So are you running network discovery? Garth's blog has a lot of SQL queries, for example: http://smsug.ca/blogs/garth_jones/archive/2009/09/14/computers-ip-and-mac-s-in-a-specific-collection.aspx
Free Windows Admin Tool Kit Click here and download it now
November 17th, 2009 12:48pm
Thanks Torsten. That link is a great starting point.
November 18th, 2009 11:29am
The actual code I used was:
SELECT Distinct
v.Name,
IP_Addresses0,
MAC.MAC_Addresses0 ,
vrs.User_Name0,
v.SiteCode
FROM
v_FullCollectionMembership v
join dbo.v_R_System vrs on v.ResourceID=vrs.ResourceID
JOIN v_RA_System_IPAddresses ON v.ResourceID = v_RA_System_IPAddresses.ResourceID
Join dbo.v_RA_System_MACAddresses MAC on MAC.ResourceID=vrs.ResourceID
WHERE
CollectionID = '<collection ID inserted here>'
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2009 7:42pm