Pour les professionnels de l’informatique >
Forums - Accueil
>
Configuration Manager General
>
ad_site_name0 definition
ad_site_name0 definition
- 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
Réponses
- 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.- Marqué comme réponseYvette OMeallyMSFT, Propriétairelundi 6 juillet 2009 20:52
- 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- Marqué comme réponseYvette OMeallyMSFT, Propriétairelundi 6 juillet 2009 20:52
Toutes les réponses
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/- 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 - 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 ? - 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 - 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 - 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.- Marqué comme réponseYvette OMeallyMSFT, Propriétairelundi 6 juillet 2009 20:52
- 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- Marqué comme réponseYvette OMeallyMSFT, Propriétairelundi 6 juillet 2009 20:52
- 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 ? - 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 - 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:] - 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- ModifiéZZ02 jeudi 16 juillet 2009 06:42
- 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
