OCS Server could not capture computer bios serial number

Bug #1206851 reported by Rodney Yeo on 2013-07-31
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OCS Inventory: Server
Undecided
Unassigned

Bug Description

At random some client computers could not show the bios serial number in OCS Server computer asset list.

I already conducted a site visit to a random computer “LSCC709COMS12” and discovered the faults for the BIOS Asset Tag not appearing in OCS Inventory NG database is due to an internal OCS Server service code bug.

The BIOS serial number is captured by the OCS Agent on the local computer but not captured in the OCS Server computer asset list.

OCS Serial Number Not Appear At Server LSCC709COMS12 PC
http://imageshack.us/photo/my-images/837/5hin.png/

OCS Serial Number Not Appear At Server LSCC709COMS12 XML
http://imageshack.us/photo/my-images/812/5ud7.png/

OCS Serial Number Not Appear At Server LSCC709COMS12 Server
http://imageshack.us/photo/my-images/801/as52.png/

Rodney Yeo

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

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?

Rodney Yeo (rodyeo) wrote :
Download full text (4.9 KiB)

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

Read more...

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

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.

Rodney Yeo (rodyeo) wrote :

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

Rodney Yeo (rodyeo) wrote :

POC to show the updated script works as intended.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers