SVM Smart Groups Not Compiling - Manually Clean the SG Generation Queue [On-Prem]

[#1]

Summary

Sometimes smart groups may get stuck and need manual intervention at the database to clear the queue and re-run the smart group generation afterward. This article teaches you to manually clear the queue that the SVM On-Prem Server uses to track smart group generation status. 

The symptoms that should lead to using this article are as follows:

1. The Smart Groups are not compiling correctly as seen in the SVM web interface. 
2. Smart Groups look like compiling, but these never complete and never updates their timestamps.
3. Smart Groups status remains "Queued for compilation" and never changes to "Completed"

Cause

The root cause of that problem is that the One-Prem server's sgdaemon (service) has encountered problems during the compilation and has therefore set a database flag that prevents other compilations from running, in order to prevent data loss.

Verify the problem

Within the "ca.csi_smartgroup_generation" table of the MySQL server installed on either Red Hat Enterprise Linux, or CentOS VA server,  the "in_progress" field displays "1" where it should normally be "0".  Perform the following actions to verify that exact problem:

1. Stop the smart group daemon service first

service sgdaemon stop [rhel6]
#or
systemctl stop sgdaemon [rhel7+]

2. Check the status of the current smart group generations at the MySQL database

mysql -u root -e "SELECT * from ca.csi_smartgroup_generation;"

3. If "in_progress" column in the table is set to "1", you have encountered this same problem. 

Resolution

You have to clear out the queue that governs the process.
Below you'll find a basic set of steps that can be taken to complete this action manually.

1. On the database server, log into your MySQL or MariaDB instance:

mysql -u root -p
#<type the password for "root" or simply press Enter if no password is set>

In the queries below, the "123456" number in the database name refers to your customer id.
If you need to figure out what this is you can run the following in our MySQL/MariaDB shell:

show databases;

2. Next, run the two queries that will clear out the queue and will release the generation to occur. 

use ca_123456;

UPDATE csi_smartgroups SET in_progress=0, generate_asap=1 WHERE in_progress=1;

use ca; 

UPDATE csi_smartgroup_generation SET in_progress=0, generate_asap=1 WHERE in_progress=1;

5. You simply need to start the sgdaemon service back up and wait for compilation of SGs to complete:

service sgdaemon start [rhel6]
#or
systemctl start sgdaemon [rhel7+]

 If the compilation hasn't completed or hasn't started on its own, try re-running it via the web interface:

If that didn't help, try running the generation manually on the application server using the cronjob:

cd /usr/local/Secunia/csi/cronjobs/

# Run this to compile all Smart Groups on all partitions:
php generate_smartgroups.php --cli --customer-id 12345678

# Or, you can run this for only a specific partition:
php generate_smartgroups.php --cli --customer-id 12345678 --partition-id 1