OCS Server could not capture computer bios serial number

I think this matter is being highlighted but not fully fixe in the latest OCS Server 2.1.0.1 release yet... ;-0

http://forums.ocsinventory-ng.org/viewtopic.php?id=5579

Matters have been highlishted and resolved as below...

http://forums.ocsinventory-ng.org/viewtopic.php?id=10239

Edit /usr/local/share/perl5/Apache/Ocsinventory/Map.pm

And add in the item with plus sign...

---BOF---
  bios => {
   mask => 2,
   multi => 0,
   auto => 1,
   delOnReplace => 1,
   sortBy => 'SSN',
   writeDiff => 0,
   cache => 0,
   fields => {
       SMANUFACTURER => {},
       SMODEL => {},
       SSN => {},
       BMANUFACTURER => {},
       BVERSION => {},
       BDATE => {},
       TYPE => {},
       ASSETTAG => {},
+ MMANUFACTURER => {},
+ MMODEL => {},
+ MSN => {},
   }
  },
---EOF---

I finally found the fault is on the WMI engine corrupted and could not retrived.

CMD> WMIC BIOS GET SERIALNUMBER /VALUE

You would get the data correct if WMI emgine is working!

There is a WMI fix batch script which can detect and fix the WMI engine automatically.

Anyone intetested?

@echo off
cls

:: Check for WMI results equal nul value then launch fix
REM
:: Get DNSHOSTNAME
FOR /F "TOKENS=2 DELIMS='='" %%A IN ('wmic OS Get csname /value') DO SET "SysHosts=%%A"
:: Get SYSTEMNAME
FOR /F "TOKENS=2 DELIMS='='" %%A IN ('wmic ComputerSystem Get Model /value') DO SET "SysNames=%%A"
:: Get SERIALNUMBER
FOR /F "TOKENS=2 DELIMS='='" %%A IN ('wmic Bios Get SerialNumber /value') DO SET "SerNumbers=%%A"
:: Get USERNAME
FOR /F "TOKENS=2 DELIMS='='" %%A IN ('wmic ComputerSystem Get UserName /value') DO SET "UserNames=%%A"
:: Get USERNAMECS
FOR /F "TOKENS=2 DELIMS='='" %%A IN ('wmic csproduct get IdentifyingNumber /value') DO SET "UserNamesCS=%%A"
:: Get MANUFACTURERCS
FOR /F "TOKENS=2 DELIMS='='" %%A IN ('wmic Bios Get Manufacturer /value') DO SET "ManufacturerCS=%%A"
:: Get VENDORCS
FOR /F "TOKENS=2 DELIMS='='" %%A IN ('wmic csproduct get Vendor /value') DO SET "VendorCS=%%A"
:: Get NAMECS
FOR /F "TOKENS=2 DELIMS='='" %%A IN ('wmic csproduct get Name /value') DO SET "NamesCS=%%A"
::
::
IF /I NOT "%SysHosts%" == "" GOTO END
IF /I NOT "%SysHosts%" == " " GOTO END
::
IF /I NOT "%SysNames%" == "" GOTO END
IF /I NOT "%SysNames%" == " " GOTO END
::
IF /I NOT "%SerNumbers%" == "" GOTO END
IF /I NOT "%SerNumbers%" == " " GOTO END
::
IF /I NOT "%UserNames%" == "" GOTO END
IF /I NOT "%UserNames%" == " " GOTO END
::
IF /I NOT "%UserNamesCS%" == "" GOTO END
IF /I NOT "%UserNamesCS%" == " " GOTO END
::
IF /I NOT "%ManufacturerCS%" == "" GOTO END
IF /I NOT "%ManufacturerCS%" == " " GOTO END
::
IF /I NOT "%VendorCS%" == "" GOTO END
IF /I NOT "%VendorCS%" == " " GOTO END
::
IF /I NOT "%NamesCS%" == "" GOTO END
IF /I NOT "%NamesCS%" == " " GOTO END
::
REM

REM Check Windows Version
::
ver | findstr /i "5\.1\." > nul
IF %ERRORLEVEL% EQU 0 goto ver_XP
::
ver | findstr /i "5\.2\." > nul
IF %ERRORLEVEL% EQU 0 goto ver_2K3
::
goto ver_ALL_Win

:ver_ALL_Win
:: Run Windows 7 specific commands here
:: Reset Repository WMI repository folder
winmgmt /standalonehost
winmgmt /resetrepository
winmgmt /verifyrepository
REM echo OS Version: Windows 7 (debug line)
goto Start

:ver_2K3
:: Run Windows 2003 specific commands here
:: Reset Repository WMI repository folder
rundll32 wbemupgd, RepairWMISetup
REM echo OS Version: Windows 2003 (debug line)
goto Start

:ver_XP
:: Run Windows XP specific commands here
:: Reset Repository WMI repository folder
rundll32 wbemupgd, UpgradeRepository
REM echo OS Version: Windows XP (debug line)
goto Start

:Start
call :StopService WSRM
call :StopService tmlisten
call :StopService iphlpsvc
call :StopService winmgmt
call :RenameWMI
call :StartService winmgmt
call :StartService iphlpsvc
call :StartService tmlisten
call :StartService WSRM
call :ImportMOF
goto :End

:RenameWMI
::
winmgmt /salvagerepository
::
Echo.
Echo Renaming the WMI repository folder
for /f "tokens=1-8 delims=:/. " %%a in ('echo %date% %time%') do set FileExtension=%%d%%b%%c%%e%%f%%g
ren "%windir%\system32\wbem\Repository" Repository.%FileExtension%.old
ren "%windir%\SysWOW64\wbem\Repository" Repository.%FileExtension%.old

Echo.
Echo Registering WMI DLLs
cd /d %windir%\system32\wbem
for /f %%s in ('dir /b /s *.dll') do echo %%s&regsvr32 /s %%s

if /i not...

#!/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
### 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

#Declaring mysql DB connection

MASTER_DB_USER='root'
MASTER_DB_PASSWD='xxxxxxxxxx'
MASTER_DB_PORT=3306
MASTER_DB_HOST='localhost'
MASTER_DB_NAME='ocsweb'

#Prepare sql query

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

#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

/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

MASTER_DB_USER=''
MASTER_DB_PASSWD=''
MASTER_DB_PORT=
MASTER_DB_HOST=''
MASTER_DB_NAME=''
SQL_Query_0=''
SQL_Query_1=''

echo "End of script"

#!/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
### 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

clear
echo "Start of script"

#Declaring mysql DB connection

MASTER_DB_USER='root'
MASTER_DB_PASSWD='xxxxxxxxxx'
MASTER_DB_PORT=3306
MASTER_DB_HOST='localhost'
MASTER_DB_NAME='ocsweb'

#Prepare sql query

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

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

#!/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
### 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='XXXXXXXXXX'
MASTER_DB_PORT=3306
MASTER_DB_HOST='localhost'
MASTER_DB_NAME='ocsweb'

#Prepare sql query

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

#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=''

echo "End of script"

#!/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
### 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='xxxxxxxxxx'
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_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_1
EOF

#Initialize mysql DB connection

MASTER_DB_USER=''
MASTER_DB_PASSWD=''
MASTER_DB_PORT=
MASTER_DB_HOST=''
MASTER_DB_NAME=''
SQL_Query_1=''

echo "End of script"

Please ignore post #5, #6, #7 due to bugs discovered and take the latest script which is bug free...

#!/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='XXXXXXXXXXX'
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 = "";'

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

After further test data done. I found some bugs and this latest update #10 seems to be working as intended. The ' is NULL ' logic could not be used and repalaced with ' = "" logic instead works best for empty string value in the field data.

Please ignore all above codes... as this new coded superceed the solution tested and working...

---BOF---
#!/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='xxxxxxxxxxx'
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---

Thanks.

Rodney

POC to show the updated script works as intended.

Reference from links...

http://ask.ocsinventory-ng.org/2180/ocsweb-database-field-updated-accountinfo-table-field_31?show=2180#q2180