åã®èšäºãžã®ã³ã¡ã³ãã§ã¯ã1Cã§ã¯ãªãExcelã§ã®äŒèšã«ã€ããŠèŠããŠããŸããã ããŠãExcelãã©ãã ãç¥ã£ãŠãããã確èªããŸãããã 仿¥ã¯ãActive DirectoryããããŒã¿ãååŸãããã¯ããšPowerShellãªãã§ïŒéåžžã®Officeã¡ã«ããºã ã®ã¿ã䜿çšããŠïŒããŒã¿ãæäœããæ¹æ³ã瀺ããŸãã ããšãã°ãMicrosoft SCOMã®ãããªãã®ããŸã ãªãå Žåã¯ãçµç¹å ã®ãªãã¬ãŒãã£ã³ã°ã·ã¹ãã ã®äœ¿çšã«é¢ããåæãç°¡åã«ååŸã§ããŸãã ããŠããŸãã¯åã«ãŠã©ãŒã ã¢ããããŠã¹ã¯ãªããããæ³šæããããã
ãã¡ãããPowerShellã®1è¡ã§ãæåéã以äžã®äŸã®ããã«ããŒã¿ãååŸã§ããŸãã ãããã第1ã«ãPowerShellã¯éå±ãããŸãã第2ã«ãExcelã¯ããŒã¿ãåçã«æŽæ°ã§ããŸããçµæã®ããã¥ã¡ã³ãã¯ãããã¯ãŒã¯äžã§å ¬éãããæŽæ°ãå¿ããããšããããŸãã
ããŒã¿ãæäœããã«ã¯ã Power Queryã¡ã«ããºã ã䜿çšããŸãã Office 2010ããã³2013ã§ã¯ã ãã©ã°ã€ã³ãã€ã³ã¹ããŒã«ããå¿ èŠããããŸããMicrosoftOffice 2016ã§ã¯ããã®ã¢ãžã¥ãŒã«ã¯æ¢ã«çµ±åãããŠããŸãã æ®å¿µãªãããæšæºçã§ã¯ååã§ã¯ãããŸãããProfessionalãå¿ èŠã§ãã
ãã®ã¡ã«ããºã èªäœã¯ãå€ãODBCãã¡ã€ã«ãããã¹ããã¡ã€ã«ããExchangeãOracleãFacebookãªã©ãããŸããŸãªãœãŒã¹ããããŒã¿ãåä¿¡ããŠââåŠçããããã«èšèšãããŠããŸãã HabrÃ©ã§æ¢ã«èšè¿°ãããŠããã¡ã«ããºã ãšçµã¿èŸŒã¿ã¹ã¯ãªããèšèªãMãã®è©³çްã«ã€ããŠã¯ãPower Queryã䜿çšããŠActive DirectoryããããŒã¿ãååŸããããã€ãã®äŸãåæããŸãã
ãŠã©ãŒã ã¢ããïŒãŠãŒã¶ãŒããã€ãã°ã€ã³ããããèŠãŠã¿ãŸããã
ãã¡ã€ã³ããŒã¿ããŒã¹èªäœãžã®ã¯ãšãªã¯ã[ããŒã¿-æ°ããã¯ãšãª-ä»ã®ãœãŒã¹ãã-Active Directoryãã]ã¿ãã§äœæãããŸãã
ããŒã¿ãœãŒã¹ãæå®ããŸãã
ãã¡ã€ã³åãéžæããæ¥ç¶ã«å¿ èŠãªããŒã¿ãæå®ããå¿ èŠããããŸãã æ¬¡ã«ããªããžã§ã¯ãã®ã¿ã€ãããã®äŸã§ã¯userãéžæããŸã ã ãã¬ãã¥ãŒãŠã£ã³ããŠã®å³åŽã§ã¯ãã¯ãšãªãæ¢ã«å®è¡ãããŠãããããŒã¿ã®ãã¬ãã¥ãŒã衚瀺ãããŠããŸãã
ãªã¯ãšã¹ããæºåãããã¬ãã¥ãŒããæ¥œãã¿ãã ããã
以åã¯ãã倿Žããã¿ã³ãã¯ãªãã¯ããŠå¿ èŠãªåãéžæããããšã§ããªã¯ãšã¹ããæºåããå¿ èŠããããŸããã åºæ¬çã«ããããã®åã¯ã¯ã©ã¹ã§ãããååã«ã¯Active Directoryãªããžã§ã¯ãã®ç¹å®ã®å±æ§ã®ã»ãããå«ãŸããŠããŸãããã¡ã€ã³ådisplayNameã¯ããèªäœã屿§ã§ãã user ã person ã topããã³securityPrincipalã¯ã©ã¹ã«çŠç¹ãåœãŠãŸãã ããã§ããæ¡åŒµãã䜿çšããŠåã¯ã©ã¹ããå¿ èŠãªå±æ§ãéžæããå¿ èŠããããŸã-åèŠåºãã«2ã€ã®ç¢å°ã®ä»ããã¢ã€ã³ã³ïŒ
- lastLogonTimestampããã³userAccountControlãéžæããŠãŠãŒã¶ãŒã¯ã©ã¹ãæ¡åŒµããŸã ã
- å人㧠telephoneNumberãéžæããŸãã
- ããã -whenCreated ;
- ããã³securityPrincipalã§ ã SamAccountName ã
ãªã¯ãšã¹ããå±éããŸãã
次ã«ããã£ã«ã¿ãŒãæ§æããŸããç¹ã«ããããã¯ãããã¢ã«ãŠã³ããååŸããªãããã«ããã«ã¯ãuserAccountControl屿§ã®å€ã512ãŸãã¯66048ã«ããå¿ èŠããããŸãããã£ã«ã¿ãŒã¯ç°å¢ã«ãã£ãŠç°ãªãå ŽåããããŸãã 屿§ã«ã€ããŠã¯ã Microsoftã®ããã¥ã¡ã³ããã芧ãã ããã
ãã£ã«ã¿ãŒãé©çšããŸã ã
Excelã¯ãããŒã¿åœ¢åŒãç¹ã«lastLogonTimestamp屿§ã®å€ã誀ã£ãŠå®çŸ©ããããšããããŸãã ãã®ãããªäžå¹žãçªç¶èµ·ãã£ãå Žåãã倿ãã¿ãã§æ£ãããã©ãŒããããèšå®ã§ããŸãã
ããã§ãuserAccountControlåãåé€ããå¿ èŠããããŸã-ãã£ã¹ãã¬ã€ã«ã¯ãŸã£ããå¿ èŠãããŸããã [ããŠã³ããŒãããŠéãã]ãã¯ãªãã¯ããŸãã
ã¿ãã¬ããã倿ããŸããã ããšãã°ãåã®ååãèªã¿ãããååã«å€æŽããŸãã èªåããŒã¿æŽæ°ãèšå®ããŸãã
ãããããã£ãã®ãããŒã¿ãã¿ãã§ãããŒãã«ãéãããšããŸãã¯ã¿ã€ã ã¢ãŠãã«ããèªåæŽæ°ãèšå®ãããŸãã
ããŒã¿æŽæ°ãæ§æããŸãã
æŽæ°ã®ã»ããã¢ãããå®äºãããã人äºéšãŸãã¯ã»ãã¥ãªãã£ãµãŒãã¹ã«ããŒãã«ãå®å šã«æž¡ãããšãã§ããŸãã誰ããã€ã·ã¹ãã ã«å ¥ã£ãããç¥ãããŸãã
let = ActiveDirectory.Domains("domain.ru"), domain.ru = {[Domain="domain.ru"]}[#"Object Categories"], user1 = domain.ru{[Category="user"]}[Objects], #" " = Table.RemoveColumns(user1,{"organizationalPerson", "shadowAccount", "posixAccount", "msExchOmaUser", "msExchBaseClass", "msExchIMRecipient", "msExchCertificateInformation", "msExchMultiMediaUser", "msExchMailStorage", "msExchCustomAttributes", "mailRecipient", "distinguishedName"}), #" securityPrincipal" = Table.ExpandRecordColumn(#" ", "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}), #" top" = Table.ExpandRecordColumn(#" securityPrincipal", "top", {"whenCreated"}, {"whenCreated"}), #" person" = Table.ExpandRecordColumn(#" top", "person", {"telephoneNumber"}, {"telephoneNumber"}), #" user" = Table.ExpandRecordColumn(#" person", "user", {"lastLogonTimestamp", "userAccountControl"}, {"lastLogonTimestamp", "userAccountControl"}), #" " = Table.SelectRows(#" user", each ([userAccountControl] = 512 or [userAccountControl] = 66048)), #" " = Table.TransformColumnTypes(#" ",{{"lastLogonTimestamp", type datetime}}), #" 1" = Table.RemoveColumns(#" ",{"userAccountControl"}) in #" 1"
ã¢ãã¬ã¹åž³ãäœæããããäŒæ¥ããŒã¿ã«ãADãšåéã§ãªãå Žåã®å¯ŸåŠæ¹æ³
Active Directoryãšçµã¿åãããŠExcelã䜿çšããå¥ã®ãªãã·ã§ã³ã¯ãADããŒã¿ã«åºã¥ããŠã¢ãã¬ã¹åž³ãäœæããããšã§ãã ãã¡ã€ã³ãæ£åžžã§ããå Žåã«ã®ã¿ãã¢ãã¬ã¹åž³ãé¢é£ããããšãæããã«ãªããŸãã
ãŠãŒã¶ãŒãªããžã§ã¯ãã®ãªã¯ãšã¹ããäœæãã mailã®ãŠãŒã¶ãŒã¯ã©ã¹ãå±éãã telephoneNumber㮠人ç©ã¯ã©ã¹ãå±éããŸãããã distinguishedNameãé€ããã¹ãŠã®åãåé€ããŸãããã¡ã€ã³æ§é ã¯ãšã³ã¿ãŒãã©ã€ãºã®æ§é ãç¹°ãè¿ããŠããããã çµç¹åäœã®ååã¯åäœã®ååã«å¯Ÿå¿ããŠããŸãã åæ§ã«ãã»ãã¥ãªãã£ã°ã«ãŒãã¯ãŠãããåã®ããŒã¹ãšããŠäœ¿çšã§ããŸãã
次ã«ã CN =ãŠãŒã¶ãŒåãOU =çµçéšãOU =éšéãDC =ãã¡ã€ã³ãDC = ruãšããè¡ãããéšéåãçŽæ¥æœåºããå¿ èŠããããŸãã ãããè¡ãæãç°¡åãªæ¹æ³ã¯ãã倿ãã¿ãã®åºåãæåã䜿çšããããšã§ãã
ããã¹ããæœåºããŸãã
åºåãæåãšããŠã OU =ããã³OU =ã䜿çšããŸã ã ååãšããŠãã³ã³ãã§ååã§ãããå®å šã«ãã¬ã€ããŸãã
åºåãæåãå°å
¥ããŸãã
ããã§ããã£ã«ã¿ãŒã䜿çšããŠããããã¯ããããŠãŒã¶ãŒãBuiltinãªã©ã®äžèŠãªOUãã«ããããŠãäžŠã¹æ¿ããæ§æã ãããŒã¿ãããŒãã«ã«èªã¿èŸŒãããšãã§ããŸãã
ãµããªãŒè¡šã®ã¿ã€ãã
ãšãŒãžã§ã³ããä»ã®ãã¬ãŒãã³ã°ãå°å ¥ããããšãªããã¯ãŒã¯ã¹ããŒã·ã§ã³ã®æ§æã«é¢ããã¯ã€ãã¯ã¬ããŒã
次ã«ãã³ã³ãã¥ãŒã¿ãŒã§ããŒã¿ãåä¿¡ããŠââã䟿å©ãªããŒãã«ãäœæããŠã¿ãŸãããã äŒç€Ÿã§äœ¿çšãããŠãããªãã¬ãŒãã£ã³ã°ã·ã¹ãã ã«é¢ããã¬ããŒããäœæããŸãããã®ããã«ãªã¯ãšã¹ããäœæããŸãããä»åã¯ããã²ãŒã¿ãŒã§ã³ã³ãã¥ãŒã¿ãŒãéžæããŸãã
ã³ã³ãã¥ãŒã¿ãŒãªããžã§ã¯ããèŠæ±ããŸãã
ã³ã³ãã¥ãŒã¿ãŒãšæäžäœã®åã¯ã©ã¹ãæ®ããŠå±éããŸãã
- cn ã operatingSystem ã operatingSystemServicePack ãããã³operatingSystemVersionãéžæããŠã ã³ã³ãã¥ãŒã¿ãŒã¯ã©ã¹ãæ¡åŒµããŸã ã
- æäžäœã¯ã©ã¹ã§ã whenCreatedãéžæããŸãã
é«åºŠãªãªã¯ãšã¹ãã
å¿ èŠã«å¿ããŠããµãŒããŒã®ãªãã¬ãŒãã£ã³ã°ã·ã¹ãã ã§ã®ã¿ã¬ããŒããäœæã§ããŸãã ããšãã°ã屿§operatingSystemãŸãã¯operatingSystemVersionã§ãã£ã«ã¿ãŒãé©çšããŸãã ç§ã¯ãããè¡ããŸããããäœææéã®è¡šç€ºãä¿®æ£ããŸã-ç§ã¯ãã®å¹Žã«ã®ã¿èå³ããããŸãã ãããè¡ãã«ã¯ã[倿]ã¿ãã§å¿ èŠãªåãéžæãã[æ¥ä»]ã¡ãã¥ãŒã§[幎]ãéžæããŸãã
ã³ã³ãã¥ãŒã¿ãŒããã¡ã€ã³ã«å
¥åããããšãããå¹ŽãæœåºããŸãã
ããã§ãäžèŠãªdisplaynameåãåé€ããçµæãããŒãããŸãã ããŒã¿ã®æºåãã§ããŸããã ããã§ãéåžžã®ããŒãã«ãšåæ§ã«äœæ¥ã§ããŸãã æåã«ã[æ¿å ¥]ã¿ãã§ããããããŒãã«ãäœæããŸã-[ããããããŒãã«]ã ããŒã¿ãœãŒã¹ã®éžæã«åæãããã®ãã£ãŒã«ããèšå®ããŸãã
ããããããŒãã«ãã£ãŒã«ãã®èšå®ã
ããã§ããã¶ã€ã³ãã«ã¹ã¿ãã€ãºããŠçµæãè³è³ããããšãã§ããŸãã
ADã®ã³ã³ãã¥ãŒã¿ãŒã®ããããããŒãã«ã
å¿ èŠã«å¿ããŠã[æ¿å ¥]ã¿ãã§ããµããªãŒãã£ãŒãã远å ã§ããŸãã ãã«ããŽãªãïŒãŸãã¯ãè¡ãïŒã§ãdata- cnã« operatingSystemã远å ããŸãã ããã¶ã€ããŒãã¿ãã§ã奜ããªãã£ãŒãã®ã¿ã€ããéžæã§ããŸããå圢ã®ãã£ãŒãããå§ãããŸãã
åã°ã©ãã
ç¶ç¶çãªæŽæ°ã«ãããããããWindows XPãæèŒããã¯ãŒã¯ã¹ããŒã·ã§ã³ãšWindows 2003ãæèŒãããµãŒããŒã®ç·æ°ãéåžžã«å€ãããšãã¯ã£ãããšããããŸãã ãããŠãåªåãããã®ããããŸãã
let = ActiveDirectory.Domains("domain.ru"), domain.ru = {[Domain="domain.ru"]}[#"Object Categories"], computer1 = domain.ru{[Category="computer"]}[Objects], #" " = Table.RemoveColumns(computer1,{"user", "organizationalPerson", "person"}), #" " = Table.SelectColumns(#" ",{"displayName", "computer", "top"}), #" computer" = Table.ExpandRecordColumn(#" ", "computer", {"cn", "operatingSystem", "operatingSystemServicePack", "operatingSystemVersion"}, {"cn", "operatingSystem", "operatingSystemServicePack", "operatingSystemVersion"}), #" top" = Table.ExpandRecordColumn(#" computer", "top", {"whenCreated"}, {"whenCreated"}), #" " = Table.TransformColumns(#" top",{{"whenCreated", Date.Year}}), #" 1" = Table.RemoveColumns(#" ",{"displayName"}) in #" 1"
ããããããã ãã§ã¯ãããŸãã
Excelã¯äŒèšçšã®ãæ°ã«å ¥ãã®ã¿ãã¬ããã ãã§ãªããäœæã§ããããšã«æ³šæããŠãã ããã çç·Žããã¢ãããŒãã«ããã圌ã¯å€æ¬¡å ããŒã¿åæïŒOLAPãã¥ãŒãïŒãšè¡åã䜿çšããé£ç«æ¹çšåŒã®è§£æ³ã®äž¡æ¹ãåŠçã§ããŸãã ãããŠãMicrosoftããã®èšŒææžãæã£ãŠãã人ãã¡ãå£ã«ã»ããããã¶ã£ãŠããŸã-3Dã²ãŒã ã§ããæ··ä¹±ãããªãã·ã§ã³ããããŸãã ãã¡ããéåœã§ã¯ãããŸããããå€ã¯ééããªãããããŸãã
Excelã管çããŒã«ãšããŠã©ãæããŸããïŒ äžèšã®ããããã䜿çšããŸãããïŒ