SharePoint + Active Directory + Bogus Accounts = Confusion

SharePoint pulls active directory accounts, easy right? But for my corporation there are tons of accounts that are not actual user accounts, but necessary for normal operation (conference room accounts, guest accounts, etc.) I had to dig through the SharePoint tables to create a solution to display not only the actual user accounts, but parsed into individual divisions for display on separate portal pages (division only names).

Deconstructing SharePoint AD pull...

I created a table, called SP_ADUsers to push data into it in a more understandable structure so I matched fields with what I needed.

The main tables in SharePoint (sharedservices database, named whatever you configured it to be named) are UserProfile and UserProfileValue. I first created a view so I could limit the userprofile ids in which I need to push into my more user-friendly table.

The sql query started like so:
--sameple view sql
select * from [sharedservicesdbname].dbo.UserProfile

This returned over 2,500 records, and after scrubbing out the bogus accounts, I am left with a little over 1,950 valid user accounts. I eliminated accounts by simply adding where clauses, both eliminating actual preferrednames or like ('%tester%') preferred names which would eliminate all tester accounts we utilize.

Next I joined (multiple times) the userprofile table to the userprofilevalue tables in a statement like so:

select Convert(VarChar,upv.PropertyVal), --firstname
Convert(VarChar,upv2.PropertyVal), --lastname
Convert(VarChar,upv3.PropertyVal), --workphone
Convert(VarChar,upv4.PropertyVal), --workemail
Convert(VarChar,upv5.PropertyVal), --office
Convert(VarChar,upv6.PropertyVal), --title
Convert(VarChar,upv7.PropertyVal), --department
Convert(VarChar,up2.PreferredName), --managername
Convert(VarChar,upv8.PropertyVal), --fax
Convert(VarChar,upv9.PropertyVal), --cellphone
Convert(VarChar,upv11.PropertyVal), --company
Convert(VarChar,upv12.PropertyVal), --city
Convert(VarChar,upv13.PropertyVal), --state
Convert(VarChar,upv14.PropertyVal), --country
Convert(VarChar,up.NTName) --login
from [sharedservicesdbname].dbo.UserProfile up
left join [sharedservicesdbname].dbo.UserProfileValue upv
on up.recordid=upv.recordid and upv.propertyid=4 --firstname
left join [sharedservicesdbname].dbo.UserProfileValue upv2
on up.recordid=upv2.recordid and upv2.propertyid=5 --lastname
left join [sharedservicesdbname].dbo.UserProfileValue upv3
on up.recordid=upv3.recordid and upv3.propertyid=8 --workphone
left join [sharedservicesdbname].dbo.UserProfileValue upv4
on up.recordid=upv4.recordid and upv4.propertyid=9 --workemail
left join [sharedservicesdbname].dbo.UserProfileValue upv5
on up.recordid=upv5.recordid and upv5.propertyid=11 --office
left join [sharedservicesdbname].dbo.UserProfileValue upv6
on up.recordid=upv6.recordid and upv6.propertyid=13 --title
left join [sharedservicesdbname].dbo.UserProfileValue upv7
on up.recordid=upv7.recordid and upv7.propertyid=14 --department
left join [sharedservicesdbname].dbo.UserProfileValue upv8
on up.recordid=upv8.recordid and upv8.propertyid=20 --fax
left join [sharedservicesdbname].dbo.UserProfileValue upv9
on up.recordid=upv9.recordid and upv9.propertyid=19 --cellphone
left join [sharedservicesdbname].dbo.UserProfileValue upv10
on up.recordid=upv10.recordid and upv10.propertyid=6
left join [sharedservicesdbname].dbo.UserProfileValue upv11
on up.recordid=upv11.recordid and upv11.propertyid=10001 --company
left join [sharedservicesdbname].dbo.UserProfileValue upv12
on up.recordid=upv12.recordid and upv12.propertyid=10002 --city
left join [sharedservicesdbname].dbo.UserProfileValue upv13
on up.recordid=upv13.recordid and upv13.propertyid=10003 --state
left join [sharedservicesdbname].dbo.UserProfileValue upv14
on up.recordid=upv14.recordid and upv14.propertyid=10004 --country
left join [sharedservicesdbname].dbo.UserProfile up2
on upv10.PropertyVal=up2.NTName and upv10.PropertyID=6
where up.recordid in (select recordid from [viewcreatedabove])

And inside a stored procedure that is run once a day (after the profile import on SharePoint) does the following:

1. Truncates the more userfriendly table I created for dataviews within SharePoint (SP_ADUsers);
2. Fills the table with the values returned from the select statement above.

Now I have a nightly updated, scrubbed active directory user list which doubles as our phone directory.