#Script to run automated sql queries for OCS Inventory NG - Update Fields_42 With SSN data which is not NULL plus Alphanumeric, Dash & UnderScore value
### ocsssnsql.sh written by Rodney Yeo of ICT Helpdesk Application Support for Taylor's Higher Education Malaysia
### and MySQL codes written by Kevin Tay of ICT Application Development for Taylor's Higher Education Malaysia
### /backup/ocsssnsql.sh > /dev/null
### Custom OCS DB Update Script
SQL_Query_0='update ocsweb.accountinfo z set z.fields_42 = (select f.ssn from ocsweb.bios f
where f.hardware_id = z.hardware_id
and f.ssn RegExp "^[A-Za-z0-9_-]+$");'
SQL_Query_1='update ocsweb.accountinfo z set z.fields_42 = (select f.ssn from ocsweb.bios f
where f.hardware_id = z.hardware_id
and f.ssn RegExp "^[A-Za-z0-9_-]+$")
where z.fields_42 is null;'
#!/usr/bin/bash
#Script to run automated sql queries for OCS Inventory NG - Update Fields_42 With SSN data which is not NULL plus Alphanumeric, Dash & UnderScore value
### ocsssnsql.sh written by Rodney Yeo of ICT Helpdesk Application Support for Taylor's Higher Education Malaysia ocsssnsql. sh > /dev/null
### and MySQL codes written by Kevin Tay of ICT Application Development for Taylor's Higher Education Malaysia
### /backup/
### Custom OCS DB Update Script
clear
echo "Start of script"
#Declaring mysql DB connection
MASTER_ DB_USER= 'root' DB_PASSWD= 'xxxxxxxxxx' DB_HOST= 'localhost' DB_NAME= 'ocsweb'
MASTER_
MASTER_DB_PORT=3306
MASTER_
MASTER_
#Prepare sql query
SQL_Query_0='update ocsweb.accountinfo z set z.fields_42 = (select f.ssn from ocsweb.bios f z0-9_-] +$");'
where f.hardware_id = z.hardware_id
and f.ssn RegExp "^[A-Za-
SQL_Query_1='update ocsweb.accountinfo z set z.fields_42 = (select f.ssn from ocsweb.bios f
where f.hardware_id = z.hardware_id
and f.ssn RegExp "^[A-Za-z0-9_-]+$")
where z.fields_42 is null;'
#mysql command to connect to database
/usr/bin/mysql -u$MASTER_DB_USER -p$MASTER_DB_PASSWD -P$MASTER_DB_PORT -h$MASTER_DB_HOST -D$MASTER_DB_NAME << EOF
$SQL_Query_0
EOF
/usr/bin/mysql -u$MASTER_DB_USER -p$MASTER_DB_PASSWD -P$MASTER_DB_PORT -h$MASTER_DB_HOST -D$MASTER_DB_NAME << EOF
$SQL_Query_1
EOF
#Initialize mysql DB connection
MASTER_DB_USER=''
MASTER_DB_PASSWD=''
MASTER_DB_PORT=
MASTER_DB_HOST=''
MASTER_DB_NAME=''
SQL_Query_0=''
SQL_Query_1=''
clear
echo "End of script"