First a warning: querying the sync database directly is a worst practice. The following script was run on a test server, and will never see my production servers. If you really want to run this on a product server the copy off the database to another server and experiment there.
DO NOT USE THIS ON PRODUCTION SERVERS
Okay, on to business. I recently had to answer the question: how many times will MA X be precedent for a given attribute? Unfortunately the consulting answer (it depends) did not fly so I went to the database to find out. The dirty little script below shows how to dig out the answer. It is only interesting because it requires the UNPIVOT statement which is not straight forward unless you happen to be David Lundell.
The script below hard codes the MA GUID, as well as the attribute I’m interested in. There may be a way more elegant script for this but I couldn’t find one so hopefully the next person that needs to answer this question will find either my post or be David ;-)
BTW – this is one of the few questions lately where I did NOT have a good PowerShell answer (at least not a supported one). PowerShell is awesome for slicing and dicing data with Select-Object and Group-Object but in its infancy just doesn’t have an Unpivot-Object cmdlet yet. This is easily forgivable since it is way cooler than TSQL will ever be, at least for an IT Pro like me.
1: ---
2: ---Objects that have UserPrincipalName attribute contributed by Exchange
3: ---
4: SELECT mailNickname
5: FROM mms_metaverse
6: WHERE object_id IN
7: (8: SELECT Object_id
9: FROM
10: (11: SELECT Object_id, userprincipalname
12: FROM mms_metaverse_lineageguid
13: ) p 14: UNPIVOT 15: (16: LineageID FOR Attribute IN
17: ( 18: userprincipalname 19: ) 20: )21: AS unpvt
22: JOIN mms_lineage_cross_reference on mms_lineage_cross_reference.lineage_id = unpvt.LineageID
23: WHERE
24: (25: mms_lineage_cross_reference.ma_id = 'AAAAAAAA-DDDD-4444-8B6A-CD6ACEA5CB49'
26: and
27: attribute = 'userprincipalname'
28: ) 29: )
No comments:
Post a Comment