Shulou(Shulou.com)06/03 Report--

Write a powershell to automate the execution of departures.

The purpose of combining mssql database is to be more automatic and save some user information to the database.

Powershell code can be executed manually or scheduled for task execution

Database (the methods I use below cannot be applied directly)

Three main tables

The employee table Userlist records the turnover status of the employee, adds a trigger, and is automatically added to the turnover table leftuser when the turnover status changes.

The leftuser table for departing employees is structured as follows, provided that there is a corresponding table between the job number and the ad account.

The group table of the terminated user is leftusergroup, which records the group that the terminated user was in before.

CREATE TABLE [dbo]. [leftUser] ([lid] [int] NULL,-- I [wid] [varchar] (3) NULL,-- Job number [workStatus] [bit] NULL,-- whether to leave [aduser] [nvarchar] (5) NULL-ad account) ON [PRIMARY] GO

Powershell code

# Author:rp722#version:6# configuration Information $Database = 'DatabaseUserName'$Server =' "ipaddress"'$UserName = 'sa'$Password =' password'# create connection object $SqlConn = New-Object System.Data.SqlClient.SqlConnection# use account to connect MSSQL$SqlConn.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName Pwd=$Password "# Open the database connection $SqlConn.open () # query the database to get the number of users in the current outgoing user table $SqlCmd = $SqlConn.CreateCommand () $SqlCmd.commandtext = 'select COUNT (aduser) from leftUser'$num=$SqlCmd.ExecuteScalar () # traversal user for ($iDeposit user I-le $num $SqlCmd = $SqlConn.CreateCommand () $SqlCmd.commandtext = "select aduser from leftUser where lid=$i" $user=$SqlCmd.ExecuteScalar () # get the user $aduser=Get-ADUser-Identity $user# to determine whether the user is enabled in AD If disabled, skip if ($aduser.enabled-eq $True) {# get the user's group $groups= (get-aduser $user-properties memberof) .memberof # traverse each group of foreach ($gp in $groups) {# get the name of the group $gp1=$gp.Split (",") [0] .split ("=") [1] # Save the corresponding relationship between the user and the group to the database $SqlCmd = New-Object System.Data.SqlClient.SqlCommand$SqlCmd.connection = $SqlConn$SqlCmd.commandtext = "insert into dbo.leftusergroup (aduser,gname) values ('$user') '$gp1') "$SqlCmd.ExecuteNonQuery () # remove user remove-adgroupmember-Identity $gp-Members $user-Confirm:$false} # Settings remarks set-aduser $aduser-Description" user has left company "# disabled group Move-ADObject $aduser-TargetPath" OU=Disabled,DC=xxxx,DC=net "# disable account Disable-ADAccount $aduser} # close database connection $SqlConn.close ()

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.