This document contains the source code for the Disaster Recovery script "USER02info.ksh".
#!/usr/bin/ksh93 ################################################################ function usagemsg { print "" print "Program: USER02info.ksh" print "" print "This script gathers user and group information from " print "each AIX server and inserts this information into " print "the MYSQL CMDB test database running on eperf." print "" print "Usage: ${1##*/} [-v] [-a] [-m machineName]" print "" print " Where '-v' = Verbose mode." print " '-a' = Gather user/group information from all" print " servers defined in the CMDB Server table" print " whose Operating System is defined as AIX." print " '-m' = Requires a single argument specifying" print " the IP name of a machine from which to" print " gather user/group information." print "" print "\"AutoContent\" enabled" print "" } ################################################################ #### #### Description: #### #### This program gathers user and group information from AIX #### servers, builds the appropriate SQL statements, then deletes #### and inserts records into the MYSQL CMDB test database running #### on eperf.tu.com #### #### The information gathered includes user names, UID numbers, #### primary group name, secondary groups, and all GID numbers. #### #### The user information includes the database record number of the #### server on which the user is configured, the user name, UID number, #### gecos information, home directory, shell, login status, #### rlogin status, and whether or not the account is locked. #### #### The group information includes the database record number of the #### server on which the group is configured, the group name, and #### GID number. #### #### Each user is configured as a member of at least one group. Each #### group to which a user is a member is identified as either the #### primary or secondary group. Only one group per user can be #### identified as primary, all other groups are secondary. #### #### Assumptions: #### #### This program assumes there is a script called "mysql.sh" in #### the current directory. This script is expected to accept SQL #### statements through standard input and perform all MYSQL database #### communications. #### #### The database tables User02, Group02, and UserGroup02 are #### assumed to already exist in the MYSQL database and are configured #### appropriately to accept the following fields - #### #### User02: server_id, name, uid, gecos, shell, login, rlogin, locked #### #### Group02: server_id, name, gid #### #### UserGroup02: server_id, user_id, group_id, grouptype #### #### See the database schema for further explanation of these fields. #### #### Dependencies: #### #### This script uses "rsh" to run commands on each machine that #### generate the user and group information. The user running this #### script must have "rsh" access to all machines for which this #### script will be executed. #### #### This script requires the "rsh" user to be a member of the #### "security" group because it accesses several pieces of #### information not available to normal users. Information such as #### "login", "rlogin", "account_locked". #### #### The output of the MYSQL SQL processor is a specific format and #### this script is expecting the data to exist in that format. If #### the SQL processor is changed, this script must be modified to #### correctly interpret the output from the SQL processor. #### #### Products: #### #### The products of this script are modified MYSQL database tables. #### This script generates the SQL statements and executes them as #### it is gathering user and group information from the AIX servers. #### Interrupting this script does NOT prevent the database from being #### modified. The database is modified as the script gathers info. #### #### Configured Usage: #### #### This script is designed to be executed from the command line #### or scheduled as a background process. #### #### Details: ################################################################ TRUE="1" FALSE="0" VERBOSE="${FALSE}" ALLMACH="${FALSE}" MACHNAME="" CMD_SQL="./mysql.sh" while getopts ":vam:" OPTION do case "${OPTION}" in 'v') VERBOSE="${TRUE}";; 'a') ALLMACH="${TRUE}";; 'm') MACHNAME="${OPTARG}";; '?') usagemsg "${0}" && exit 1 ;; ':') print "ERROR: Option '-${OPTARG}' requires an argument:" usagemsg "${0}" && exit 2 ;; esac done shift $(( ${OPTIND} - 1 )) trap "usagemsg ${0}" EXIT if (( ALLMACH == FALSE )) && [[ "_${MACHNAME}" = "_" ]] then print "ERROR: Either '-a' or '-m' must be specified" exit 3 fi if (( ALLMACH == TRUE )) && [[ "_${MACHNAME}" != "_" ]] then print "ERROR: Options '-a' and '-m' are mutually exclusive" exit 4 fi trap "-" EXIT ################################################################ TMPFILE01="/tmp/tmp01${$}.out" TMPFILE02="/tmp/tmp02${$}.out" ################################################################ #### #### This script is currently configured to be executed from #### a specific machine in a specific directory i.e. #### #### eperf:/home/french/mysql #### #### This will be changed in future iterations of the script, but #### for now thats just the way it is. #### ################################################################ cd /home/french/mysql ################################################################ #### #### Check to see if the user identified a specific machine #### from which to gather user and group information, or #### requested the script to run gather infor for all machines. #### Depending upon what the user specified, the server name #### and database record number for each server is selected from #### the database and output to a temporary file. #### ################################################################ if (( ALLMACH == TRUE )) then print "SELECT id,name FROM Server02 WHERE osname LIKE '%AIX%'\n\\g" | ${CMD_SQL} 2>/dev/null | sed -e "1,1 d" > "${TMPFILE01}" else print "SELECT id,name FROM Server02 WHERE name = '${MACHNAME}'\n\\g" | ${CMD_SQL} 2>/dev/null | sed -e "1,1 d" > "${TMPFILE01}" fi ################################################################ #### #### Read each server ID number and server name from the temporary #### file and delete all records from the database associated with #### the server. This will ensure that old or deleted user names, #### group names, or group types are removed and only the current #### info will exist when the script is finished. #### ################################################################ while read ID SERVER do print "DELETE FROM User02 WHERE server_id = '${ID}'\n\\g DELETE FROM Group02 WHERE server_id = '${ID}'\n\\g DELETE FROM UserGroup02 WHERE server_id = '${ID}'\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### #### Build a while loop to be executed on each remote machine #### via "rsh" This loop will read each field of data from the #### /etc/passwd file and process each field. The loop is used #### to extract additional information for each user not contained #### within the /etc/passwd construct. This information includes #### the login status, rlogin status, account locked status, the #### primary and secondary group. The group information is obtained #### using the 'id' command. The information gathered for each #### user and an executeable line of information is built. This #### executable line is constructed to define several shell variables - #### #### uname, ugecos, uhome, ushell, login, rlogin, account_locked #### and several variables as returned by the 'id' command. #### ################################################################ CMD='while IFS=":" read uname x2 x3 x4 ugecos uhome ushell; do [[ "_${uname}" != "_" ]] && print "uname=${uname} ugecos=$( print ${ugecos} | sed -e '"'"'s/[ ][ ]*/_/g'"'"' ) uhome=${uhome} ushell=${ushell} $( /usr/sbin/lsuser -a login ${uname} | awk '"'"'{ print $2 }'"'"' ) $( /usr/sbin/lsuser -a rlogin ${uname} | awk '"'"'{ print $2 }'"'"' ) $( /usr/sbin/lsuser -a account_locked ${uname} | awk '"'"'{ print $2 }'"'"' ) $( id ${uname} )"; done < /etc/passwd' (( VERBOSE == TRUE )) && print -u2 "# ${SERVER} : ${CMD}" ################################################################ #### #### A ping test is performed to determine if the target machine #### is network accessible. If it can not be pinged, it is skipped. #### ################################################################ if ping -c 2 -w 5 ${SERVER} > /dev/null 2>&1 then ################################################################ #### #### If the ping test is successful, the previously built while #### loop command is executed on the remote machine. The result of #### the while loop is a series of executable korn shell statements. #### These statements are redirected to a temporary file and will #### be read and executed one line at a time. #### #### Some processing of the while loop output is required. The id #### command output contains opening and closing parenthesis, and #### are substituted with colon ':' characters, which will be #### interpreted later as field separators. #### #### In order to suppress the special meaning of the whitespace #### characters, double quotes are inserted into the line at various #### locations. A double quote character is inserted after each #### equal sign '='. A double quote character followed by a semi- #### colon is inserted after each space character to designate the #### end of the value and the beginning of the next variable assignment. #### #### The end of each line is terminated with a double quote to end #### the open quote associated with the last variable assignment #### on the line. #### #### The 'id' command returns the list of secondary groups to which a #### user belongs formatted as follows - #### #### groups=2(bin),3(sys),7(security),8(cron)... #### #### In a previous substitution, the open/close parenthesis were #### replaced with a colon ':'. To ensure the data is formatted properly #### for processing, a substitution is performed to replace a colon ':' #### followed by either a double quote character or a comma with #### only the double quote character or comma. The colon is removed. #### ################################################################ rsh ${SERVER} "${CMD}" | sed -e "s/[()]/:/g;s/=/=\"/g;s/ /\"; /g;s/$/\"/g;s/:\([\",]\)/\1/g" > "${TMPFILE02}" ################################################################ #### #### All user and group information has now been gathered from #### a remote machine. The information has been parsed and formatted #### into executable korn shell statements and stored in a temporary #### file, each line containing all information for a single user. #### #### Read each line from the temporary file and build the appropriate #### SQL delete/insert statements for each database record. #### ################################################################ while read LINE do ################################################################ #### #### Initialize the uid, gid, and groups variables at the beginning #### of each loop iteration so that values from the previous #### iteration are not carried forward. #### ################################################################ uid="" gid="" groups="" ################################################################ #### #### Each line read from the temporary file is constructed as an #### executable line of korn shell code. Execute the line using #### the eval command. ################################################################ eval ${LINE} ################################################################ #### After the line is executed, several shell variables will be #### defined. One of these variables is called 'uid' and will #### contain two pieces of information, the UID number and the #### user name associated with this UID number. The fields of #### the 'uid' variable are separated using a colon ':' character #### and are separated into their individual components using #### the korn shell deletion operators. #### ################################################################ USERNAME="${uid##*:}" USERID="${uid%%:*}" (( VERBOSE == TRUE )) && print -u2 "# server_id:${ID} user:${USERID}(${USERNAME})" ################################################################ #### #### For the current data line, delete any existing records from #### the User database table associated with this username on #### the server being processed. The delete SQL statement is #### piped into the MYSQL SQL interpreter for processing. #### ################################################################ print "DELETE FROM User02 WHERE server_id = '${ID}' AND name = '${USERNAME}'\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### #### For the current data line, insert a record into the User #### database table for the username currently being processed on #### the server being processed. The insert SQL statement is #### piped into the MYSQL SQL interpreter for processing. #### ################################################################ print "INSERT INTO User02 ( server_id, name, uid, gecos, home, shell, login, rlogin, locked ) VALUES ( '${ID}', '${USERNAME}', '${USERID}','${ugecos//_/ }', '${uhome}', '${ushell}', '${login}', '${rlogin}', '${account_locked}' )\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### #### Another variable containing information is called 'gid' and #### contains two pieces of information, the GID number and the #### group name associated with this GID number. The fields of #### the 'gif' variable are separated using a colon ':' character #### and are separated into their individual components using #### the korn shell deletion operators. #### ################################################################ GROUPNAME="${gid##*:}" GROUPID="${gid%%:*}" ################################################################ #### #### The database record id number for this groupname on the server #### currently being process is selected and saved in a shell variable. ################################################################ print "SELECT id FROM Group02 WHERE server_id = '${ID}' AND name = '${GROUPNAME}'\n\\g" | ${CMD_SQL} 2>/dev/null | sed -e "1,1 d" | read GROUP_NUM ################################################################ #### If an database record is found, an SQL insert statement is #### constructed for this groupname on the server currently #### being processed. The SQL statement is piped into the #### MYSQL SQL interpreter for processing. #### ################################################################ if [[ "_${GROUP_NUM}" = "_" ]] then (( VERBOSE == TRUE )) && print -u2 "# server_id:${ID} user:${USER_NUM}(${USERNAME}) group:${GROUP_NUM}(${GROUPNAME})" print "INSERT INTO Group02 ( server_id, name, gid ) VALUES ( '${ID}', '${GROUPNAME}', '${GROUPID}')\n\\g" | ${CMD_SQL} 2>/dev/null fi ################################################################ #### #### In preparation for building the server/user/group/grouptype #### associations, the database record id number for this previously #### inserted username is selected and saved in a shell variable. ################################################################ print "SELECT id FROM User02 WHERE server_id = '${ID}' AND name = '${USERNAME}' AND uid = '${USERID}'\n\\g" | ${CMD_SQL} 2>/dev/null | sed -e "1,1 d" | read USER_NUM ################################################################ #### Also needed is the database record id number for this previously #### inserted groupname, which is selected and saved in a shell variable. ################################################################ print "SELECT id FROM Group02 WHERE server_id = '${ID}' AND name = '${GROUPNAME}' AND gid = '${GROUPID}'\n\\g" | ${CMD_SQL} 2>/dev/null | sed -e "1,1 d" | read GROUP_NUM ################################################################ #### #### Delete any existing records from the UserGroup database table #### associated with this user and group on the server being #### processed. The delete SQL statement is piped into the MYSQL #### SQL interpreter for processing. #### ################################################################ print "DELETE FROM UserGroup02 WHERE server_id = '${ID}' AND user_id = '${USER_NUM}' AND group_id = '${GROUP_NUM}'\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### #### Insert a record into the UserGroup database table that provides #### and association between each server, user, and primary group #### to which the user is a member. #### ################################################################ print "INSERT INTO UserGroup02 ( server_id, user_id, group_id, grouptype ) VALUES ( '${ID}', '${USER_NUM}', '${GROUP_NUM}', 'primary')\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### #### Now begin processing of any secondary group that may be associated #### with the user. Secondary groups are contained in a shell variable #### called 'groups'. Check to see if this shell variable contains #### a value, if so parse the contents of the 'groups' variable into #### korn shell executable statements . #### ################################################################ if [[ "_${groups}" != "_" ]] then ################################################################ #### #### Since the secondary groups associated with each user may #### consist of 0 or more values, prepare an associative array #### to contain the secondary group values. Begin by unsetting #### the shell variable to contain the secondary groups associative #### array. Unsetting the array will insure no values from other #### iterations of the loop are carried forward to this iteration. #### Define the associative array and build an executable korn #### shell statement from the values contained in the 'groups' #### shell variable. #### #### The 'groups' shell variable may contain 0 or more values, the #### values separated by commas ','. Each value may consist of #### 2 fields, each field separated by colon ':' characters. The #### contents of the 'groups' shell variable is piped through the #### sed command to reconfigure the values into korn shell executable #### statements that initialize associative array values for each #### secondary group. #### #### After the executable statement is built, it is executed using #### the 'eval' command. The associative array is saved for #### processing until all user lines from the temporary file #### have been read and processed. #### ################################################################ unset GROUPS_${USERNAME} eval typeset -A GROUPS_${USERNAME} GLINE=$( print ${groups} | sed -e "s/^/GROUPS_${USERNAME}\[/g;s/:/\]=/g;s/,/; GROUPS_${USERNAME}\[/g" ) eval ${GLINE} fi ################################################################ #### #### This iteration of the user loop is now complete, and the #### next iteration can begin. #### ################################################################ done < "${TMPFILE02}" fi ################################################################ #### #### In preparation for processing the secondary groups associated #### with each user, select the database record id numbers and #### user names for the server currently being processed. Loop #### through each selected user saving the selected values in #### shell variables. #### ################################################################ print "SELECT id,name FROM User02 WHERE server_id = '${ID}'\n\\g" | ${CMD_SQL} 2>/dev/null | sed -e "1,1 d" | while read USER_NUM USERNAME do ################################################################ #### #### Process the secondary groups for each user, one user at a time #### by looping through the associative array containing the #### secondary group names and GIDs. Create a list of group #### GIDs to loop through by extracting the indices from the #### associative array containing the secondary groups for this #### user. ################################################################ LIST="" eval LIST="\"\${!GROUPS_${USERNAME}[@]}\"" for i in ${LIST} do ################################################################ #### Save the secondary group name in a temporary shell variable. ################################################################ eval GTMP="\"\${GROUPS_${USERNAME}[\${i}]}\"" ################################################################ #### #### In preparation for inserting the secondary groups into the #### Group database table, perform a select for the group to #### see if it already exists. ################################################################ print "SELECT id,name FROM Group02 WHERE server_id = '${ID}' AND name = '${GTMP}' AND gid = '${i}'\n\\g" | ${CMD_SQL} 2>/dev/null | sed -e "1,1 d" | read GROUP_NUM GROUPNAME if [[ "_${GROUP_NUM}" = "_" ]] then ################################################################ #### If the secondary group already exists in the Group database #### table for the server currently being processed, delete it. ################################################################ print "DELETE FROM Group02 WHERE server_id = '${ID}' AND name = '${GTMP}'\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### Obtain the GID number from the secondary group associative #### array for user and group currently being processed. ################################################################ eval GNUM="\"\${!GROUPS_${USERNAME}[\${i}]}\"" ################################################################ #### Now, insert a record into the Group database table for this #### secondary group. ################################################################ print "INSERT INTO Group02 ( server_id, name, gid ) VALUES ( '${ID}', '${GTMP}', '${GNUM}')\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### #### In preparation for inserting a record in the UserGroup database #### table, obtain the database record id number for the #### secondary group that was just inserted into the Group #### database table. #### ################################################################ print "SELECT id,name FROM Group02 WHERE server_id = '${ID}' AND name = '${GTMP}' AND gid = '${i}'\n\\g" | ${CMD_SQL} 2>/dev/null | sed -e "1,1 d" | read GROUP_NUM GROUPNAME (( VERBOSE == TRUE )) && print -u2 "# server_id:${ID} group_num:${GROUP_NUM}(${GROUPNAME})" fi (( VERBOSE == TRUE )) && print -u2 "# server_id:${ID} \ user:${USER_NUM}(${USERNAME}) \ group:xx(xx) \ secondary:${GROUP_NUM}(${GROUPNAME})" ################################################################ #### If a record for the secondary group already exists in the #### UserGroup database table, delete it. #### ################################################################ print "DELETE FROM UserGroup02 WHERE server_id = '${ID}' AND user_id = '${USER_NUM}' AND group_id = '${GROUP_NUM}'\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### #### Insert a record into the UserGroup database table to #### associate the secondary group with a user and server. #### ################################################################ print "INSERT INTO UserGroup02 ( server_id, user_id, group_id, grouptype ) VALUES ( '${ID}', '${USER_NUM}', '${GROUP_NUM}', 'secondary')\n\\g" | ${CMD_SQL} 2>/dev/null ################################################################ #### #### Go to the next iteration of the GID loop. #### ################################################################ done ################################################################ #### #### Go to the next iteration of the User loop. #### ################################################################ done ################################################################ #### #### Go to the next iteration of the Server loop. #### ################################################################ done < "${TMPFILE01}" ################################################################ #### #### Cleanup temporary files. #### ################################################################ rm -f "${TMPFILE01}" rm -f "${TMPFILE02}"
This file last modified 02/04/09