Script Source Code for "USER02info.ksh"

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 mtxdoc."
  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 mtxdoc.mtxia.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.
#### 
#### mtxdoc:/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 11/02/10