I need to create an SCCM report to show last logged on user on all machines and the Active Directory department attribute of that last logged on user.

Hope this help.. [Assuming that ‘Active Directory User Discovery’ enabled in your environment ]

---------

select distinct top 1000 sys.Netbios_name0, sys.user_name0,SYS.ad_site_name0,usr.company0, usr.department0
from v_r_system_Valid sys
left outer JOIN v_r_user usr ON sys.user_name0 = usr.user_name0

First you will need to add the Active Directory attribute "department" to Active Directory User Discovery since that attribute is not discovered by default.

Then you should be able to use the report query that Sachin lists.

First you will need to add the Active Directory attribute "department" to Active Directory User Discovery since that attribute is not discovered by default.

yup...department attribute need to be add .. like this

I need to create an SCCM report to show last logged on user on all machines and the Active Directory department attribute of that last logged on user.

You will have add custom attribute as mentiond by sachin. Take a look at this thread explaining berifly how to do it and process explains it with reporting as well.

http://wmug.co.uk/blogs/dotraphael/archive/2010/10/10/sccm-2007-understanding-active-directory-attribute-discovery.aspx

So I've added the department attribute into the AD User Discovery and it is available as a column in the view v_R_User.  I do not know how to add the department column into the SQL statement below.  I get a syntax error (I am definitely a noob when it comes to SQL).  Any help would be much appreciated!

---select * from v_GS_SYSTEM_ENCLOSURE

SELECT  distinct
CS.name0 as 'Computer Name',
CS.domain0 as 'Domain',
CS.UserName0 as 'User',
USR.department0 as 'Department',
BIOS.SerialNumber0 as 'Bios serial',
SE.SerialNumber0 as 'System Enclosure serial',
CS.Manufacturer0 as 'Manufacturer',
CS.Model0 as 'model',
OS.Caption0 as 'OS',
RAA.SMS_Assigned_Sites0 as 'Site',
RAM.TotalPhysicalMemory0 as 'Total Memory',
sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
CPU.MaxClockSpeed0 as 'Max CPU Speed',
CPU.Name0 as 'CPU Model',
CPU.Is64Bit0 as '64 Bit Compatible'
from 
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID
right join v_R_User USR on USR.ResourceID = CS.ResourceID
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
where
LDisk.DriveType0 =3
group by
CS.Name0,
CS.domain0,
CS.Username0,
USR.department0,
BIOS.SerialNumber0,
SE.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
RAA.SMS_Assigned_Sites0,
RAM.TotalPhysicalMemory0,
CPU.MaxClockSpeed0,
CPU.Name0,
CPU.Is64Bit0
ORDER BY CS.name0

You problem is here. right join v_R_User USR on USR.ResourceID = CS.ResourceID

USR.ResourceID != CS.ResourceID, you need to map the username to the user logon to the PC. By using the user’s department information you will end up with unreliable results.

Anyways you need to make these changes to your query.

left join v_R_User USR on USR.Unique_User_Name0 = CS.UserName0

Hope this help.. [Assuming that Active Directory User Discovery enabled in your environment ]

---------

select distinct top 1000 sys.Netbios_name0, sys.user_name0,SYS.ad_site_name0,usr.company0, usr.department0
from v_r_system_Valid sys
left outer JOIN v_r_user usr ON sys.user_name0 = usr.user_name0

What if you want to run this query for a specific collection (rather than everyone) and in the report you want to be prompted for the collection?

Sure you can do that, just use the v_FullCollectionMembership view and filter on the collection ID.

Has anyone been able to translate this to 2012? I'm trying to get this running and the Report Builder is not liking it too much.

Hi Garth,

In SCCM 2012 we enabled Active Directory User Discovery and added company and department details but still we are unable to query from the table, please suggest.

• Proposed as answer by Md Imran Pasha 2 hours 11 minutes ago
• Unproposed as answer by Md Imran Pasha 2 hours 11 minutes ago

Hi Garth,

In SCCM 2012 we enabled Active Directory User Discovery and added company and department details but still we are unable to query from the table, please suggest.

• Proposed as answer by Md Imran Pasha Friday, February 06, 2015 9:51 AM
• Unproposed as answer by Md Imran Pasha Friday, February 06, 2015 9:51 AM

Hi Garth,

In SCCM 2012 we enabled Active Directory User Discovery and added company and department details but still we are unable to query from the table, please suggest.

• Proposed as answer by Md Imran Pasha Friday, February 06, 2015 9:51 AM
• Unproposed as answer by Md Imran Pasha Friday, February 06, 2015 9:51 AM

Hi Garth,

I have ran a  full AD user discovery and I have waited for 12 hours nearly and still I am unable to find the table. Thanks a lot for the reply. Please suggest on how to proceed further.

did you look at adusrdis.log for the newly added attributes ? any errors found ? use search option for the respective attribute to find from log.These custom attributes will be added to the existing user tables/views but do not create new tables.

Hi Eswar,

Thanks a lot for the reply. I checked the log file and I get the error as "WARN:  Failed to get following optional attributes, department,company,". Please suggest what to do next.

seems the attribute value for specific user do not have the info set in AD but can you try to run the following query against your SQL DB to see if any info published ? recheck once again.

select * from v_r_user ? see if custom attributes added or not.

read this link http://www.verboon.info/2013/10/configmgrfailed-to-get-following-optional-attributes-warning-in-adsysdis-lo

Hi Eswar,

The Link you provided talks of Active Directory System Attributes and not User Attributes. Please suggest on how to proceed. Please share your mail id so that I can send you my query.

Hi Eswar,

When I ran select *from v_r_user I can see the department and company but the column shows null value for everything. Please suggest on next step.

HI Everyone,

Can any 1 give me query for 1. Installed Software and Version, Deployment Status and Deployment Date. Thanks in advance.