ad_site_name0 definition

Thu, 02 Jul 2009 08:38:23 Z

Hi all,

I am trying to create a report of all machines discovered by SCCM and group them by country, region
I couldn't find any country, region info in the database but I found "ad_site_name0" in table V_R_System.
It looks like the country-site combination retrieved from Active Directory(eg:"CA-TOR" stands for Canada-Toronto) but I am not sure.
Anyone knows how SCCM collect data for this fields?

Does SCCM collect AD info(say:DN) and store it somewhere ?

Thanks

ad_site_name0 definition

Thu, 02 Jul 2009 10:28:59 Z

The list of which attributes that ConfigMgr is collecting can be found on the AD Attribute tab for both AD System Discovery and AD User Discovery.

http://www.enhansoft.com/

ad_site_name0 definition

Thu, 02 Jul 2009 13:52:53 Z

This attribute corresponds to the name that your AD administrators assigned to the AD sites that the machine is a member of. Systems have no way of knowing what country that they are in unless you tell them where they are.

Jason | http://myitforum.com/cs2/blogs/jsandys | Twitter @JasonSandys

ad_site_name0 definition

Fri, 03 Jul 2009 01:34:41 Z

Thanks Garth and Jason !

I am trying to modify one of the default Asset Intelligence Report
License 01A - Microsoft license ledger for Microsoft License Statements
so it grouped by Country, Region.

I thought may be I can use the value of "Canonical name of object"(found under object tab if I select the property of a computer in Active Directory Users and Computers under Admin tools)
"Canonical name of object" is a string like this: Domain/CA/TOR/Computers/machineName

Does SCCM store it somewhere in the database ?

ad_site_name0 definition

Fri, 03 Jul 2009 03:21:09 Z

If you open the Active Directory System Discovery Properties from the Discovery Methods node under Site Settings and go to the Active Directory tab, all of the attributes captured by default are displayed. You can add distinguishedName to this tab -- you actually want the distiguished name of the object and not the canonical name, the cn only lists the last part of the name whereas the dn lists the entire fully qualified name.

Jason | http://myitforum.com/cs2/blogs/jsandys | Twitter @JasonSandys

ad_site_name0 definition

Fri, 03 Jul 2009 04:18:52 Z

I need to rewite that report in TSQL. Where can I find these info(DN etc) in the SCCM database ?

Also I didn't get the following comments:
If you open the Active Directory System Discovery Properties from the Discovery Methods node under Site Settings and go to the Active Directory tab, all of the attributes captured by default are displayed.
I couldn't find the above bold items in Configuration Manager Console

Thanks

ad_site_name0 definition

Fri, 03 Jul 2009 11:53:42 Z

Are you basically looking for the OU a computer object is in?

Check out this: http://www.myitforum.com/forums/m_171634/tm.htm , the entry from jnelson993

Standardize. Simplify. Automate.

ad_site_name0 definition

Fri, 03 Jul 2009 18:22:12 Z

You first have to add the dn to the AD discovery: http://www.screencast.com/users/JasonSandys/folders/Default/media/6f77ae5a-3686-4f69-97b1-fc1c8ce585e3.

Although note that the DN is not the ADsPath either which is what "Domain/CA/TOR/Computers/machineName" is. The ADsPath isn't actually an attribute of an object. So if that's truly what you want, you can try using the OU returned by the AD System Group discovery (as suggested by Sherry); however, it does not only return the OU that the object live in, it returns all of the parent OUs also.

If you add dn to the discovery it will eventually live in v_SMS_R_SYSTEM. If you choose to use OUs, they live in v_RA_System_SystemOUName.

Jason | http://myitforum.com/cs2/blogs/jsandys | Twitter @JasonSandys

ad_site_name0 definition

Tue, 07 Jul 2009 03:06:41 Z

Thanks Jerry and Jason.Very helptul tips.
One last questions. I extract the country codes from System_OU_Name0 recutrned from v_RA_System_SystemOUName and compared them with the country nodes in Active Directory Users and Computers. It seems that there are less records collected in the SCCM database(27 vs 36). how can have have a full number of country codes returned ?

ad_site_name0 definition

Tue, 07 Jul 2009 03:19:20 Z

You probably do not have any discovered resources in those OUs: either the OU itself is empty, does not contain computer accounts (if you are only using system discovery), or the computer accounts that are in that OU were not resolvable to an IP address.

Also, the OU memberships for non-assigned systems will not collected, so its possible that the systems in those OUs are not assigned to the site.

For detailed information of what is and is not being discovered, check the adsysdis.log and the adsgrpys.log for info.

Jason | http://myitforum.com/cs2/blogs/jsandys | Twitter @JasonSandys

ad_site_name0 definition

Tue, 07 Jul 2009 05:23:08 Z

Many thanks for all the helpers so far.
I modify the original License 01A - Microsoft license ledger for Microsoft License Statements to the following SQL.
There are only few products returned when I try to test run a report by assigning a country code.
It doesn't even return product "Windows XP" but I am sure there are windowsXP installed for the specified country code.
What's wrong with it?

----------------------------------------------------------------------------------------------------------------------
declare @CodeOption int
if len(@ChannelCode) = 0 OR @ChannelCode IS NULL
    select @CodeOption = 0
else if @ChannelCode = 'Not Available'
    select @CodeOption = 1
else
    select @CodeOption = 2
Select
        max(substring(v_RA_System_SystemOUName.System_OU_Name0,19,2)),
        max(substring(v_RA_System_SystemOUName.System_OU_Name0,22,3)),
        v_LU_MSProd.MLSFamilyName AS [Licensed Family Name],
        v_LU_MSProd.MLSProductName AS [Licensed Product Name],
        v_LU_MSProd.VersionCode AS [Version Code],
        count(v_GS_INSTALLED_SOFTWARE_MS.ResourceID) AS [Inventoried Quantity],
        CASE when (CAST(v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0 as varchar) is NULL) then 'Not Available' Else    CAST(v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0 as varchar) End AS [Channel],
        @CollectionID AS [Collection ID]
from v_GS_INSTALLED_SOFTWARE_MS
Left Join v_LU_MSProd on v_LU_MSProd.MPC = v_GS_INSTALLED_SOFTWARE_MS.MPC0
inner join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = v_GS_INSTALLED_SOFTWARE_MS.ResourceID
inner join v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_MS.ResourceID
left JOIN dbo.v_RA_System_SystemOUName on v_RA_System_SystemOUName.ResourceID = v_GS_INSTALLED_SOFTWARE_MS.ResourceID
Where    v_LU_MSProd.MLSProductName is NOT NULL and    v_FullCollectionMembership.CollectionID = @CollectionID
        and (    @CodeOption = 0 or    (@CodeOption = 1 and v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0 IS NULL) OR    (@CodeOption = 2 and v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0 = @ChannelCode)    )
Group by v_LU_MSProd.MLSFamilyName, v_LU_MSProd.MLSProductName, v_LU_MSProd.VersionCode, v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0
having
        (max(v_RA_System_SystemOUName.System_OU_Name0) like '%/'+@CountryCode+'/%' OR @CountryCode is null OR len(@CountryCode)=0)
        and (max(v_RA_System_SystemOUName.System_OU_Name0) like '%/'+@RegionCode+'/%' OR @RegionCode is null OR len(@RegionCode)=0)
order by max(substring(v_RA_System_SystemOUName.System_OU_Name0,19,2)),max(substring(v_RA_System_SystemOUName.System_OU_Name0,22,3)),v_LU_MSProd.MLSFamilyName,v_LU_MSProd.MLSProductName
Select 'For information on selecting the optimal volume license program for your needs, visit: ' as [Additional Information:] union all Select 'http://www.microsoft.com/Licensing/MPLA' as [Additional Information:]

ad_site_name0 definition

Thu, 16 Jul 2009 06:24:18 Z

What are the differences between v_R_System_Valid.ad_site_name0 and v_RA_System_SystemOUName.System_OU_Name0
They both contain AD country and site info. I built 2 versions of license report by using each of them as following.

**v_R_System_Valid.ad_site_name0 Version **
Select substring(v_R_System_Valid.ad_site_name0,1,2) as CountryCode,
        substring(v_R_System_Valid.ad_site_name0,4,3) as SiteCode,
        v_LU_MSProd.MLSFamilyName AS [Licensed Family Name],
        v_LU_MSProd.MLSProductName AS [Licensed Product Name],
        v_LU_MSProd.VersionCode AS [Version Code],
        CASE when (CAST(v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0 as varchar) is NULL) then 'Not Available' Else CAST(v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0 as varchar) End AS [Channel],
        count(v_GS_INSTALLED_SOFTWARE_MS.ResourceID) AS [Inventoried Quantity]
from    v_GS_INSTALLED_SOFTWARE_MS
        Left Join v_LU_MSProd on v_LU_MSProd.MPC = v_GS_INSTALLED_SOFTWARE_MS.MPC0
        inner join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = v_GS_INSTALLED_SOFTWARE_MS.ResourceID
        inner join v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_MS.ResourceID
Where    v_LU_MSProd.MLSProductName is NOT NULL and ad_site_name0 is not null and substring(v_R_System_Valid.ad_site_name0,1,2)='AU'
group by substring(v_R_System_Valid.ad_site_name0,1,2),substring(v_R_System_Valid.ad_site_name0,4,3),v_LU_MSProd.MLSFamilyName,v_LU_MSProd.MLSProductName,v_LU_MSProd.VersionCode,v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0
order by CountryCode,SiteCode,v_LU_MSProd.MLSFamilyName,v_LU_MSProd.MLSProductName,v_LU_MSProd.VersionCode,v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0

**v_RA_System_SystemOUName.System_OU_Name0 Version**
Select
        substring(v_RA_System_SystemOUName.System_OU_Name0,19,2) as CountryCode,
        substring(v_RA_System_SystemOUName.System_OU_Name0,22,3) as SiteCode,
        v_LU_MSProd.MLSFamilyName AS [Licensed Family Name],
        v_LU_MSProd.MLSProductName AS [Licensed Product Name],
        v_LU_MSProd.VersionCode AS [Version Code],
        CASE when (CAST(v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0 as varchar) is NULL) then 'Not Available' Else CAST(v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0 as varchar) End AS [Channel],
        count(v_GS_INSTALLED_SOFTWARE_MS.ResourceID) AS [Inventoried Quantity]
from    v_GS_INSTALLED_SOFTWARE_MS
        Left Join v_LU_MSProd on v_LU_MSProd.MPC = v_GS_INSTALLED_SOFTWARE_MS.MPC0
        inner join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = v_GS_INSTALLED_SOFTWARE_MS.ResourceID
        inner join v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_MS.ResourceID
        inner join v_RA_System_SystemOUName on v_RA_System_SystemOUName.ResourceID = v_GS_INSTALLED_SOFTWARE_MS.ResourceID
Where    v_LU_MSProd.MLSProductName is NOT NULL and ad_site_name0 is not null --and substring(v_RA_System_SystemOUName.System_OU_Name0,19,2)='AU'
group by substring(v_RA_System_SystemOUName.System_OU_Name0,19,2),substring(v_RA_System_SystemOUName.System_OU_Name0,22,3),v_LU_MSProd.MLSFamilyName,v_LU_MSProd.MLSProductName,v_LU_MSProd.VersionCode,v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0
having len(substring(v_RA_System_SystemOUName.System_OU_Name0,19,2))>0 and len(substring(v_RA_System_SystemOUName.System_OU_Name0,22,3) )>0
order by CountryCode,SiteCode,v_LU_MSProd.MLSFamilyName,v_LU_MSProd.MLSProductName,v_LU_MSProd.VersionCode,v_GS_INSTALLED_SOFTWARE_MS.ChannelCode0

they gave me different results. Which on is correct ?
Thanks

ad_site_name0 definition

Thu, 16 Jul 2009 12:13:57 Z

That information is retrieved from your Active Directory and populated by your Active Directory folks. Only they can tell you how accurate it is.

Jason | http://myitforum.com/cs2/blogs/jsandys | http://blogs.catapultsystems.com/jsandys/default.aspx | Twitter @JasonSandys