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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics