Welcome to OCS Inventory NG community support, where you can ask questions and receive answers from other members of the community.

Please ask questions only in English or French.

Release 2.5 available

The official documentation can be found on http://wiki.ocsinventory-ng.org. Read it before asking your question.

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

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

 

ocs serialnumber servicetag

ocsweb bios ssn

 

ocsweb accountinfo field_31

 

asked in Core server development by (320 points)
edited by

4 Answers

0 votes
Hi

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

Regards

Frank
answered by (69.9k points)
0 votes
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.
answered by (320 points)
0 votes

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

answered by (320 points)
edited by
+1 vote

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

answered by (320 points)
edited by
 
Powered by Question2Answer
...