Client scans not listed under Completed Scans after sent to SVM on-prem server

Client scans not listed under Completed Scans after sent to SVM on-prem server

Description

You have performed scanning of clients with the Software Vulnerability Manager after installing a brand new server, or after performing a server upgrade from a lower version, but the scans are not being returned in the Web UI view under Completed Scans after these are sent to the server. At first sight, the Agent/SVM does not indicate a technical problem with sending the scan result. 

Troubleshooting

Further investigation of the log data in /var/log/messages log available on the SVM application server reveals the following informative error that helps you identify a problem with the server processing:

Error while checking in with server (500) : "HTTP/1.1 500 Internal Server ErrorReplication Scenario

The /var/log/messages log also reveals concrete error related to the database indicating that some database tables are missing, or have not been constructed during the installation/upgrade as it is expected. The error message will normally look similar to what's below, but it may inform you on a different database table in each specific scenario depending on where the upgrade/install went wrong:

Psr\Log\AbstractLogger->error: Exception: Exception caught: 'PDOException' [ SQLSTATE[42S02]: Base table or view not found: 1146 Table 'vuln_track.sr_product_secure_ranges' doesn't exist ]#012Query Failed: 'SELECT product_id,eol, vuln_id,version_secured FROM vuln_track.sr_product_secure_ranges WHERE product_id=xxxxx AND match_data='x.x.x.x' AND match_kind = 1 AND platform = 1 ORDER BY version_secured DESC, vuln_id DESC'

Additional Troubleshooting

Find out the latest patch update applied to your server. The problem might have been caused due to incomplete upgrade/installation to the last server patch version. If you need more information on what is the latest patch version, you can open a Forum thread in the Flexera Community asking about it:

  • Enter your MySQL database on the SVM Database Server
mysql -u root -p <password> 
  • Find the relevant validation csi_version, patch_version, date_applied, and locked information
select * from ca.csi_pdb_info;

In the event you discover the patch_version to be lower than the latest patch version of SVM, or you see the locked state equaling to "1", follow the resolution steps we have listed below by starting up with rerunning the installationProcess.sh script and completing the full server configuration at once. 

If the patch version is OK, then the problem has already been identified to be a missing database table that hasn't been constructed correctly (or has been issued in a broken state) during the migration/installation of the server. Take the following steps after making note from the error message the exact table name that appears missing or broken, to confirm the root cause.

We would use sr_product_secure_ranges database table to illustrate the steps:

use vuln_track;
  • Check of the table announced as missing in the error message is there, and if it can be accessed:
show columbns from sr_product_secure_ranges;
  • The above command should show you the available table columns if the table is not broken.
  • If the table is broken or missing, you'll get an error that basically confirms the root cause. 

on-prem troubleshooting missing table 1.PNG

  • If the query returned the actual columns, keep troubleshooting further. 
    • Try executing exactly the same query that was displayed in the /var/log/messages log:
SELECT product_id,eol, vuln_id,version_secured FROM vuln_track.sr_product_secure_ranges WHERE product_id=xxxxx AND match_data='x.x.x.x' AND match_kind = 1 AND platform = 1 ORDER BY version_secured DESC, vuln_id DESC
  • If the query fails again, try spotting any syntax error in the name of the table/query.
  • If the database table name is correct, try simplifying the query to see if it will execute differently.
  • If all attempts result in the same error message and behavior, then a corruption is likely. 

Resolution

To resolve corruption of a database table, or to restore a missing one that has failed to create accordingly in the event that your server has not upgraded to the latest patch version, or it simply failed during the table creation process, there are two concrete steps that can be taken to resolve this:

  1.  Rerun the server configuration script in an attempt to complete the server installation/upgrade. 
  2. Drop the databases that have a missing or corrupted table and have them restored automatically.

Solution #1

Solution #1 should be OK to apply at your convenience, as long as you are careful to not misconfigure your server from its default configuration that can lead to additional settings. 

cd /usr/local/Secunia/csi/install
sh ./InstallationProcess.sh 

re-run the installation process on-prem.PNG

Solution #2

Solution #2 is not recommended before you have consulted with Flexera support as there may be gotchas you need to be aware of specific to your particular case scenario and server configuration.

You should be free to perform the database drop in the event that you have made a recent backup that can be restored, and when the database you must drop out is "vuln_track" following these next steps:

a) Stop the relevant system services before you proceed, as follows:

systemctl stop sgdaemon
systemctl stop httpd
systemctl _dstop crond

b) Login to MySQL with privileged account and drop the relevant databases in MySQL, as follows:

DROP database replication_metadata;
DROP database vuln_track;
Exit

c) Run manual synchronization to restore the database (ensure that the server has an uninterrupted persistent network connection that will not lose any packets for a continuous period of time);

cd /usr/local/Secunia/csi/cronjobs/
PHP crontab_importer.php

d) Wait until the databases have been restored.

This may take a while depending on how big the downloaded files are. It can take up to a few hours for a very large database and it could finish for a few minutes for a fresh install. You should be seeing the database dump download happening on your screen in real-time as you run the crontab_importer.

e) When the database import is done, confirm that within the database as well.
The result of the below command should show the "Next Update" equaling to "Binlog":

SELECT * FROM replication_metadata.update_status;

f) You can also run the below command to confirm the "last database generation date":

SELECT max(vuln_create_date) FROM vuln_track.vuln;

Here's how the whole process looks like [this example has been manually edited to fit into one graphic]:

drop database example.png

g) After completion of the above process, you should exit the database and start the relevant services again before you can use your server:

systemctl start sgdaemon
systemctl start httpd
systemctl start crond
Was this article helpful? Yes No
No ratings
Version history
Revision #:
3 of 3
Last update:
‎Nov 22, 2019 02:35 PM
Updated by:
 
Contributors