How could OCSWEB Database BIOS Table SSN Field, be updated into ACCOUNTINFO Table FIELD_31;

Question

Dear OCS Developers,

This could be a good feature to be implemented into OCS next release.

How could OCSWEB Database BIOS Table SSN Field, be updated into ACCOUNTINFO Table FIELD_31;

This is because BIOS SSN often gets missing when WMI engine fails.

So my approach is to copy the BIOS SSN data field and paste it into FIELD_31 of ACCOUNTINFO field to capture the static data to ensure it is not recapture as missing again.

 

Structure
---------
COPY FROM SOURCE...
database = ocsweb, table = bios, field = SSN,

TO DESTINATION...
database = ocsweb, table = accountinfo, field = field_31,

 

Logic
-----
COPY FROM SOURCE... "SSN" to TO DESTINATION... "field_31"

 

MySQL Statement
---------------

 

 

 

Answer 1

Hi

Thanks for your proposition. It will be better to register a blueprint => https://launchpad.net/ocsinventory-server

Regards

Frank

Answer 2

Or could there be a php hard code logic to check if "Serial Number" or "SSN" field already exist then do not override it with blank or null value. Which is in many cases due to failure corrruption of WMI engine.

Answer 3

Dear All,

 

This is the solution to my query... it was a inhouse develop solution... I will put this code in a Linux Shell Script and Cron job to schedule a nightly auto update.

 

---BOF SQL Code---

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 or z.fields_42 = "" ;
---EOF SQL Code ---

 

Please note FIELDS_42 is created via OCS Application for database consistency.

 

Thanks.

Rodney

ICT Help Desk Application Support

Taylor's Higher Education

Malaysia

Answer 4

Put this file in Linux Cron job to trigger it... ;-)

 

--- BOF ocs-ssn-update.sh ---

#!/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
### https://bugs.launchpad.net/ocsinventory-server/+bug/1206851
### 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

echo "Start of script"

#Declaring mysql DB connection
MASTER_DB_USER='root'
MASTER_DB_PASSWD='XXXXXXXX'
MASTER_DB_PORT=3306
MASTER_DB_HOST='localhost'
MASTER_DB_NAME='ocsweb'

#Prepare sql query
# Update all data with Alpha Numberic, Dash and UnderScore but exclude NULL data
SQL_Query='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 or z.fields_42 = "" ;'

#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
EOF

#Initialize mysql DB connection
MASTER_DB_USER=''
MASTER_DB_PASSWD=''
MASTER_DB_PORT=''
MASTER_DB_HOST=
MASTER_DB_NAME=''
SQL_Query=''

echo "End of script"

--- EOF ocs-ssn-update.sh ---