对接SAP人事档完成AD账号的自动授权
1、功能介绍
SAP上线后提供人事异动主档查询,通过本地建立数据库,对异动人事主档进行每日的资料整理,串接出每日的新增、离职、异动人员。将此类资料转成csv模式,提供ftp服务供AD服务器抓取。
AD服务器抓取每日资料后通过编写powershell脚本进行账号的自动授权处理。
2、环境
AD环境:Windows server 2003
SAP异动主档库:SqlServer 2012
本地库:SqlServer 2012
操作AD脚本环境:ActiveRolesManagementShellforActiveDirectoryx86_130.msi
3、详细
人员离职
VIEW_SAP:本地库进行连接服务器创建,连接之SAP提供的人事异动档库,并串接出当日最晚一笔异动档资料
SELECT a.MANDT, a.PERNR, a.BUILD_DATE, a.BUILD_TIME, a.LAST_CHG, a.ENAME, a.BUKRS, a.FIRST_HDATE, a.LAST_WDATE, a.HIRE_DATE, a.LEAVE_DATE, a.TRFGR, a.TRFST, a.TRFGR_DATE, a.PRE_ORGEH, a.ORGEH, a.OSHORT, a.OSTEXT, a.ZORGLONG, a.STELL, a.STELL_BEGDA, a.PLANS, a.KOSTL, a.ZLOCALID, a.SCHKZ, a.MASSN, a.ACTION_CODE, a.ZZAUSW, a.WERKS, a.ZORGJGID, a.ZOPADMINID FROM HCMPRD.hcmprd.dbo.HRPA02_WJ AS a INNER JOIN (SELECT PERNR, BUILD_DATE, MAX(BUILD_TIME) AS time FROM HCMPRD.hcmprd.dbo.HRPA02_WJ AS HRPA02_WJ_1 WHERE (BUILD_DATE = CONVERT(varchar(100), GETDATE(), 112)) GROUP BY BUILD_DATE, PERNR) AS b ON a.BUILD_DATE = b.BUILD_DATE AND a.BUILD_TIME = b.time AND a.PERNR = b.PERNR WHERE (a.BUILD_DATE = CONVERT(varchar(100), GETDATE(), 112))
infra栏位介绍:
PDPT:人员所在部门编码 INFM:账号权限 PART:部门名称 PDPTUP:所在部门的上阶部门
VIEW_ADdisableuser:通过此视图,本地即建立了人员与AD的对应关系表
SELECT RIGHT(a.PERNR, 7) AS name, a.ENAME AS givenname, RIGHT(a.PERNR, 7) + '@test.com.cn' AS UserPrincipalName, RIGHT(a.PERNR, 7) AS SAMACCOUNTNAME, RIGHT(a.PERNR, 7) + ' ' + a.ENAME AS displayname, a.ORGEH + '/' + a.ENAME AS description, 'test' AS company, a.STELL AS title, b.PART AS department, '"CN=' + b.INFM + ',OU=GlobalGroups,OU=CustomGroups,DC=test,DC=COM,DC=CN"' AS gonggongcao, 'lcmuser.bat' AS dengrujiaoben, 'K:' AS lianjiedao, '\\wufile1.test.com.cn\personal\' + RIGHT(a.PERNR, 7) AS kdizhi, '"OU=' + b.PDPTUP + ',OU=MASTER GROUP,DC=test,DC=COM,DC=CN"' AS path, 'test' AS password, 'test\' + RIGHT(a.PERNR, 7) AS [IDENTITY], a.PERNR AS employeeID, 'W' + RIGHT(a.PERNR, 7) + '@test.com.cn' AS mail FROM dbo.View_sap AS a INNER JOIN dbo.infra AS b ON a.ORGEH = b.PDPT WHERE (a.ACTION_CODE = 'D') #D栏位为离职人员的定义栏位
BCP脚本:每天通过sqlserver的bcp工具,定时生成csv资料
bcp "select col1 = 'NAME',col2 = 'GIVENNAME',col3='USERPRINCIPALNAME',col4='SAMACCOUNTNAME',col5='DISPLAYNAME',col6='DESCRIPTION',col7='COMPANY',col8='TITLE',col9='DEPARTMENT',col10='GONGGONGCAO',col11='DENGRUJIAOBEN',col12='LIANJIEDAO',col13='KDIZHI',col14='PATH',col15='PASSWORD',col16='IDENTITY',col17='EMPLOYEEID',col18='MAIL' union all select * from wuhcmint.dbo.view_ADdisableuser" queryout D:\data\disableuser\addisableuser.csv -t "," -w -Usa -P***** -S"wumssql\wuhcmint"
csv资料:
NAME,GIVENNAME,USERPRINCIPALNAME,SAMACCOUNTNAME,DISPLAYNAME,DESCRIPTION,COMPANY,TITLE,DEPARTMENT,GONGGONGCAO,DENGRUJIAOBEN,LIANJIEDAO,KDIZHI,PATH,PASSWORD,IDENTITY,EMPLOYEEID,MAIL 1150027,扫地僧,[email protected],1150027,1150027 扫地僧,00001409/扫地僧,TEST,28000200,少林寺,"CN=GO_EN,OU=GlobalGroups,OU=CustomGroups,DC=TEST,DC=COM,DC=CN",user.bat,K:,\\file.test.com.cn\personal\1150027,"OU=00001400,OU=MASTER GROUP,DC=TEST,DC=COM,DC=CN",password,test\1150027,31150027,[email protected]
AD架构:
AD服务器上FTP脚本编写,每日进行资料抓取
FTP -n -s:SCRIPT2.DAT @echo %date:~0,4%%date:~5,2%%date:~8,2% up ok >>ftplog2.txt open ****** USER administrator ****** lcd D:\AD\ADadduser ASCII quote type c 1208 get adduser/adadduser.csv lcd D:\AD\ADdisableuser ASCII #这里注意相关的转码 quote type c 1208 get disableuser/addisableuser.csv quit
powershell脚本:
#Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue; $SourceFile = "D:\AD\ADdisableuser\addisableuser.csv"; $LogFile = "D:\AD\ADdisableuser\LOG.txt"; $ErrorFile = "D:\AD\ADdisableuser\Error.txt"; Import-Csv $SourceFile | Foreach{ $user2= Get-QADuser -Name $_.NAME -SearchRoot "OU=Master Group,DC=test,DC=com,DC=cn" -SizeLimit 0 | Select-Object name if ($user2.Name -ne $null ) { Disable-QADUSer -identity $_.IDENTITY; move-QADObject -identity $_.IDENTITY -NewParentContainer 'test.com.cn/Master Group/DeniedUser'; $Date=Get-Date $ExportContent += $_.NAME + " 账号已注销 " + $Date + "`r`n"; Out-File -InputObject $ExportContent -FilePath $LogFile -Append } else { $Date=Get-Date $E += $_.NAME + " 无此账号 " + $Date + "`r`n"; Out-File -InputObject $E -FilePath $LogFile -Append } $ExportContent= "" }
定期执行powershell的bat:调用ActiveRolesManagementShellforActiveDirectory工具
C:\WINDOWS\system32\WINDOW~2\v1.0\POWERS~1.EXE -psconsolefile "%ProgramFiles%\Quest Software\Management Shell for AD\ConsoleSettings.psc1" -command ". 'D:\AD\ADdisableuser\disableuser1.ps1'"
人员新增
具体SQL不再做详细介绍
powershell脚本:
#Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue; $SourceFile = "D:\AD\ADadduser\adduser.csv"; $LogFile = "D:\AD\ADadduser\LOG.txt"; $ErrorFile = "D:\AD\ADadduser\Error.txt"; Import-Csv $SourceFile | Foreach{ $user2= Get-QADuser -Name $_.NAME -SearchRoot "OU=Master Group,DC=test,DC=com,DC=cn" -SizeLimit 0 | Select-Object name if ($user2.Name -eq $null ) { New-qaduser -Name $_.NAME -LastName $_.NAME -GivenName $_.GIVENNAME -UserPrincipalName $_.USERPRINCIPALNAME -SamAccountName $_.SAMACCOUNTNAME -DisplayName $_.DISPLAYNAME -Description $_.DESCRIPTION -Company $_.COMPANY -Title $_.TITLE -Department $_.DEPARTMENT -ParentContainer $_.PATH -UserPassword $_.PASSWORD; Set-QADUSer -identity $_.IDENTITY -UserMustChangePassword $True -LogonScript $_.DENGRUJIAOBEN -HomeDirectory $_.KDIZHI -HomeDrive $_.LIANJIEDAO; Add-QADGroupMember -identity $_.GONGGONGCAO -Member $_.NAME; $Date=Get-Date $ExportContent += $_.NAME + " 账号已建立" + $Date + "`r`n"; Out-File -InputObject $ExportContent -FilePath $LogFile -Append } else { $Date=Get-Date $E += $_.NAME + " 账号已存在" + $Date + "`r`n"; Out-File -InputObject $E -FilePath $LogFile -Append } $ExportContent= "" }
3、人员异动
具体SQL不再做详细介绍
powershell脚本:
#Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue; $SourceFile = "D:\AD\ADmoveuser\admoveuser.csv"; $LogFile = "D:\AD\ADmoveuser\LOG.txt"; $ErrorFile = "D:\AD\ADmoveuser\Error.txt"; Import-Csv $SourceFile | Foreach{ $user2= Get-QADuser -Name $_.NAME -SearchRoot "OU=Master Group,test,DC=com,DC=cn" -SizeLimit 0 | Select-Object name $testMANAGER = $_.MANAGER if ($user2.Name -ne $null ) { move-QADObject -identity $_.IDENTITY -NewParentContainer $_.PATH Get-QADuser -identity $_.NAME -SearchRoot "OU=Master Group,DC=test,DC=com,DC=cn" -SizeLimit 0 | Select-Object MemberOf |Format-Custom -Property * >"D:\AD\ADmoveuser\tmp.txt" Select-String D:\AD\ADmoveuser\tmp.txt -pattern "CN" |select-object Line >"D:\AD\ADmoveuser\tmpa.txt" $member=Get-Content D:\AD\ADmoveuser\tmpa.txt $linenum=(get-content D:\AD\ADmoveuser\tmpa.txt).count-1 $i=3 for($i=3;$i -le $linenum;$i++) { Remove-QADGroupMember -identity $member[$i] -Member $_.IDENTITY } Add-QADGroupMember -identity $_.GONGGONGCAO -Member $_.IDENTITY; if (($testMANAGER -ne "0") -and ($testMANAGER -ne "H20") -and ($testMANAGER -ne "H30")) { Add-QADGroupMember -identity "CN=test_Manager,OU=GlobalGroups,OU=CustomGroups,DC=test,DC=COM,DC=CN" -Member $_.IDENTITY; } else { $Date=Get-Date $D += $_.NAME + " 非主管" + $Date + "`r`n"; Out-File -InputObject $D -FilePath $LogFile -Append } Set-QADUSer -identity $_.IDENTITY -Description $_.DESCRIPTION -Title $_.TITLE -Department $_.DEPARTMENT ; $Date=Get-Date $ExportContent += $_.NAME + " 账号已异动" + $Date + "`r`n"; Out-File -InputObject $ExportContent -FilePath $LogFile -Append } else { $Date=Get-Date $E += $_.NAME + " 无此帐户" + $Date + "`r`n"; Out-File -InputObject $E -FilePath $LogFile -Append } $ExportContent= "" }
转载于:https://blog.51cto.com/voidyao000/1932648