Hi, I'm pooling AD to discover my computers and I also need to pull the "managed by" field of each computer account in order to know who is the owner of each machines in SCCM. Everything goes fine. The problem is that the managed by value returned is in "CN=username,OU=xxxx,OU=xxx,DC=xxxx" format. This is not usefull when creating reports. (I want to display computers information linked with user information.) Is there a way to convert the returned CN value into clear text directly in the SCCM database? If not, the other solution would be to use a ExtentionAttribute to store this value but it's duplicating information that's already there... Thanks, Ben

Just in reporting, correct?maybe something like this, you're just messing with the formatting of the output to the report, not the underlying data itself. fyi, this might not be the best way to format that... but I'm no sql guru. That honor belongs to a lot of people, but I'm not 1 of them! sys.Managedby, SUBSTRING(sys.Managedby,CHARINDEX('CN=',sys.Managedby)+3,(charindex('OU=',sys.Managedby)-CHARINDEX('CN=',sys.Managedby)-4)) as 'Just The Name'from v_r_system sysStandardize. Simplify. Automate.

Also, I'd consider gathering that piece of information as an extended attribute or custom data import if you have a bazillion clients. Performing all that string manipulation on a few thousand records is fine, but when you've got 300K machines and a bunch of joins, then things start bogging down. But if you've got 300K clients, then you've likely already got someone around that could do that, so...Number2 - (John Nelson)Microsoft MVP (2009) - System Center Configuration Managerhttp://number2blog.com

So, are you doing AD user discovery? If you are, then you can just skip the string manipulation and use the distinguishedName of the ManagedBy field of the computer as a join field on the dbo.v_R_User view. Now, I don't think we're collecting the ManageBy data for computers, so I'm not sure if that's just on the dbo.v_R_System_Valid view or what, but assuming it is, if you just did something like this it might just be what you need for better reporting without string manipulation: SELECT sysv.Netbios_Name0 AS ComputerName, usr.Name0 AS ManagerName FROM dbo.v_R_System_Valid sysv JOIN dbo.v_R_User usr ON sysv.ManagedBy = usr.distinguishedName0Number2 - (John Nelson)Microsoft MVP (2009) - System Center Configuration Managerhttp://number2blog.com

Just a guess, but I don't think it's in _valid. I think it's only in v_r_system. I think this might be it, #2 (knowing I don't have a way to test this, because none of our labs pull ManagedBy, and even if they did, I don't know if AD would have data in that field anyway):SELECT REPLACE(SUBSTRING(sys1.managedby0,4,CHARINDEX(',OU',sys1.managedby0,3)-4),'\,',',')FROM dbo.v_R_system sys1So Ben, just make sure we've guessed the right attribute of managedby0, and guessed the right view of v_r_system... but the formatting of the replace I know will be spot on. (Because it's #2 saying so. He's got m@d sql skillz.)Standardize. Simplify. Automate.