刚刚我咨询与整个USERPROFILE AD用户信息提取到一个CSV文件从客户端的有趣的要求。
,它发生在我,我大概可以让他们从quot出口;中央Administrationquot - "管理用户Profilesquot;节,然而,作为需求的一部分,他们也想有向上提取一部分作为管理细节和用户报告。
坏消息! B计划显然需要编写代码解决方案,以出口从网站集级别的所有用户级别的详细信息,然后复制它们,把它们放在一起。然而,它仍然不为根据事实上,很多UP的用户可能不会签署所有网站集合的用户的目的。
{S0的}
B计划的备份计划,我下楼的UserProfile_DB数据库和UserProfile_Full的表。我深知,这只是不是最好的做法,但在这种情况下,我只是不能想出比这更好的方法。
最后,这是今天早上我来到了,为了履行我的任务查询:SELECT a.NTName, a.Employee, g.FirstName, h.LastName,
b.Title, e.Department, a.[Office Phone], c.Email, f.Office, a.Manager
,(select top 1 f.Email from UserProfile_Full f
where f.NTName = a.Manager
group by f.NTName, f.Email) as 'Manager Email'
FROM (select
a.NTName,
a.RecordID,
a.Manager,
a.PreferredName as Employee,
b.PropertyVal as [Office Phone]
from UserProfile_Full a, UserProfileValue b
where b.PropertyID=8 and
a.RecordID=b.RecordID) a -- WorkPhone
left outer join
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as Title
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=13 and
a.RecordID=b.RecordID) b -- Title
on a.RecordID=b.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Email
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=9 and
a.RecordID=b.RecordID) c -- WorkEmail
on a.RecordID=c.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as [Cell Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=19 and
a.RecordID=b.RecordID) d --CellPhone
on a.RecordID=d.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Department
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=14 and
a.RecordID=b.RecordID) e --Department
on a.RecordID=e.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Office
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=11 and
a.RecordID=b.RecordID) f --Office
on a.RecordID=f.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as FirstName
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=4 and
a.RecordID=b.RecordID) g --FirstName
on a.RecordID=g.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as LastName
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=5 and
a.RecordID=b.RecordID) h --LastName
on a.RecordID=h.RecordID
order by a.NTName
表示,这是不是一个漂亮的解决方案,但是当你正在运行的选项,它总是可以作为一个备份计划;)利用|埃里克・雪(brokensnow)