{"id":84,"date":"2007-12-19T10:22:22","date_gmt":"2007-12-19T10:22:22","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=84"},"modified":"2009-04-05T08:01:32","modified_gmt":"2009-04-05T08:01:32","slug":"updating-ad-attributes-from-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/updating-ad-attributes-from-an-excel-spreadsheet","title":{"rendered":"Updating AD attributes from an Excel spreadsheet"},"content":{"rendered":"

You don’t always have access to a proper IdM system. At the moment I’m at an organisation which still uses the old, manual ways of updating AD. I was asked if I knew an easy way to update the mobile phone numbers of a list of users, the data having been sent to Helpdesk in a spreadsheet. I knew that a long list of departments were shortly to be changed as well, so I figured I’d make a general solution, where you could also specify the attribute name in the spreadsheet.<\/p>\n

I actually ended up writing the script twice.<\/p>\n

<\/p>\n

    \n
  1. First I used DNs as the search criteria, which makes the script quite simple, and also means it will work with any object type. You can see that script here<\/a>.<\/li>\n
  2. Then, after seeing that the source spreadsheet contained usernames rather than DNs, I modified the script to use the CN instead. The restriction is that now the script only works with user objects (though there’s actually no reason why you couldn’t make a column for the object type in the spreadsheet as well). That script is here<\/a>.<\/li>\n<\/ol>\n

    It needs to be noted that these scripts should only be used for updating single-valued attributes. I have included no back-out<\/em> – so if you have the wrong data in your spreadsheet I accept no responsibility for you putting rubbish in your AD.<\/p>\n

    You also need to make sure your spreadsheet is tidy, with even column lengths, and no blank row in the middle (the script stops at the first blank cell).<\/p>\n

    For the CN script your spreadsheet should look something like this:<\/p>\n\n\n\n\n\n
    jbloggs<\/td>\ndepartment<\/td>\nIT Helpdesk<\/td>\n<\/tr>\n
    jbloggs<\/td>\nmobile<\/td>\n0123456789<\/td>\n<\/tr>\n
    hzhen<\/td>\ndescription<\/td>\nAdministrative Assistant<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

    You don’t always have access to a proper IdM system. At the moment I’m at an organisation which still uses the old, manual ways of updating AD. I was asked if I knew an easy way to update the mobile phone numbers of a list of users, the data having been sent to Helpdesk in…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[24,38,10],"tags":[],"class_list":["post-84","post","type-post","status-publish","format-standard","hentry","category-ad","category-excel","category-vbscript"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-1m","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/84","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=84"}],"version-history":[{"count":1,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/84\/revisions"}],"predecessor-version":[{"id":464,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/84\/revisions\/464"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=84"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=84"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=84"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}