OCS Inventory: Server

OCS Server could not capture computer bios serial number

Bug #1206851 reported by Rodney Yeo
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OCS Inventory: Server
New
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

Revision history for this message
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

Revision history for this message
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---

Revision history for this message
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?

Revision history for this message
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...

Revision history for this message
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"

Revision history for this message
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"

Revision history for this message
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"

Revision history for this message
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"

Revision history for this message
Rodney Yeo (rodyeo) wrote :

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

Revision history for this message
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"

Revision history for this message
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.

Revision history for this message
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

Revision history for this message
Rodney Yeo (rodyeo) wrote :

POC to show the updated script works as intended.

Revision history for this message
Rodney Yeo (rodyeo) wrote :

Reference from links...

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

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

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.