Wednesday, December 29, 2010

Dirty SQL Query to Get MV Attribute Details

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: