QRadar

QRadar: How do I get rid of status messages in the log source app?

Disclaimer: This is my own personal blog and wiki and should not be treated as official advice.

Refreshing log source status

Log source status should be updated by toggling the log source or restarting ingress. This allows the log sources to reconnect.

Sometimes, that doesn't work and the database needs a manual nudge. Follow these steps:

  1. Find the service provider (spconfig) ID from sensordevice so you modify the correct log source.
  2. Find the record ID from sensorprotocolconfig.
  3. Update last_known_status in sensorprotocolstatus.
  4. Clear any messages in sensorprotocolstatusentry.

Finding the spconfig ID

The easiest way to find the spconfig for a log source is to use the ID from the Log Source Management app.

For example, this log source is an Azure Event Hubs log source with an ID of 1916.

Run:

psql -U qradar -c "select spconfig from sensordevice where id='1916';"

 spconfig
----------
 22

Finding the record ID from sensorprotocolconfig

Now that we have the spconfig, we can get the record ID that is used to manage the actual entries for the log source:

psql -U qradar -x -c "select * from sensorprotocolconfig where id='22';"

-[ RECORD 1 ]-+---------------------------
id | 22
spid | 78
configname | Event Hubs Gateway Example
defaultconfig | f
status_record | 10

The status_record value denotes what the ID is for the next few tables. Note that the record ID is unique to each log source, but only active protocols (i.e. non-syslog) will have one.

What's up with sensorprotocolstatus?

Using the status_record value from above, we can take a look at how the status is represented:

psql -U qradar -x -c "select * from sensorprotocolstatus where id='10';"

-[ RECORD 1 ]-----+--------------
id | 10
start_time | 1673376346940
end_time | 1673376347194
last_known_status | 3
current_state | 3

Not a lot here, but we do have two fields that look interesting,last_known_status and current_state. To figure out what these values represent, let's take a look at the table references:

psql -U qradar -x -c "\d+ sensorprotocolstatus;"
                                                    Table "public.sensorprotocolstatus"
      Column       |  Type  |                               Modifiers                               | Storage | 
Stats target | Description-------------------+--------+-----------------------------------------------------------------------+--------
+--------------+------------
 id                | bigint | not null default nextval('sensor_protocol_status_sequence'::regclass) | plain   
|              |
 start_time        | bigint | not null                                                              | plain   
|              |
 end_time          | bigint | not null                                                              | plain   
|              |
 last_known_status | bigint | not null                                                              | plain   
|              |
 current_state     | bigint | not null                                                              | plain   
|              |
Indexes:
    "sensor_protocol_status_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "current_state_fkey" FOREIGN KEY (current_state) REFERENCES sensorprotocolstatusstate(id)
    "last_known_status_fkey" FOREIGN KEY (last_known_status) REFERENCES sensorprotocolstatusseverity(id)
Referenced by:
    TABLE "sensordevice" CONSTRAINT "sensor_device_status_fkey" FOREIGN KEY (status_record) REFERENCES 
sensorprotocolstatus(id)
    TABLE "sensorprotocolconfig" CONSTRAINT "sensor_protocol_status_fkey" FOREIGN KEY (status_record) 
REFERENCES sensorprotocolstatus(id)
    TABLE "sensorprotocolstatusentry" CONSTRAINT "sps_fkey" FOREIGN KEY (sps_id) REFERENCES sensorprotocolstatus
(id)
Triggers:
    replicate_track_activity_public_sensorprotocolstatus_trigger AFTER INSERT OR DELETE OR UPDATE ON 
sensorprotocolstatus FOR EACH ROW EXECUTE PROCEDURE replicate_track_activity_public_sensorprotocolstatus()
    replicate_track_truncate_public_sensorprotocolstatus_trigger BEFORE TRUNCATE ON sensorprotocolstatus FOR 
EACH STATEMENT EXECUTE PROCEDURE replicate_track_truncate_public_sensorprotocolstatus()

From here, we can tell that last_known_status and current_state refer to two completely different tables, let's have a look at those tables to resolve what
the values represent:

psql -U qradar -x -c "select * from sensorprotocolstatusstate;"

-[ RECORD 1 ]------
id          | 1
description | INIT
-[ RECORD 2 ]------
id          | 2
description | ACTIVE
-[ RECORD 3 ]------
id          | 3
description | CLOSED

psql -U qradar -x -c "select * from sensorprotocolstatusseverity;"

-[ RECORD 1 ]-----
id          | 1
severity    | 1
description | INFO
-[ RECORD 2 ]-----
id          | 2
severity    | 2
description | WARN
-[ RECORD 3 ]-----
id          | 3
severity    | 3
description | ERROR

Current_state is not what is shown in the UI but actually describes whether the protocol is starting up or not. Those three states aren't actually present in the UI or API response, so we'll ignore this field.

That means last_known_status is what we are looking for here and in this case the status was 3 which means "ERROR".

To set it from ERROR to normal, we want a status of 1. To do so, let's update the table:

psql -U qradar -x -c "update sensorprotocolstatus set last_known_status='1' where id='10';"

-[ RECORD 1 ]-----+-------------
id                | 10
start_time        | 1673376346940
end_time          | 1673376347194
last_known_status | 1
current_state     | 3

After that, my log source is in 'OK' status but still shows some error messages. We don't want those, we want this log source to be error message free.
Taking another look at the sensorprotocolstatus relationships, we see another table mentioned that we haven't looked at yet:

TABLE "sensorprotocolstatusentry" CONSTRAINT "sps_fkey" FOREIGN KEY (sps_id) REFERENCES sensorprotocolstatus
(id)

Clearing the messages

We know there's another table related to the status, that uses a column named sps_id that is the same as the ID we've been using, the spconfig

Taking a look at the table, we can see the following:

psql -U qradar -c "select * from sensorprotocolstatusentry where sps_id='10';"

  id   | sps_id |   timestamp   | severity |                                                                                                           
status_text                                          
                                                                 -------+--------+---------------+---------
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 47266 |     10 | 1673376347185 |        3 | Could not parse text as a URI reference. For more information see 
the "Raw Error Message". The query threads for this log source will be stopped. To re-enable 
the query threads, disable the log source and then re-enable it.
 47267 |     10 | 1673376347185 |        3 | The Storage Account URI is malformed. Ensure that the Storage 
Account information is valid and properly formatted. Unable to connect to the host.
 47268 |     10 | 1673376347185 |        3 | Raw Error Message : Illegal character in authority at index 8: 
https://[Storage Account Name].blob.core.windows.net
 47269 |     10 | 1673376347185 |        3 | java.net.URISyntaxException: Illegal character in authority at 
index 8: https://[Storage Account Name].blob.core.windows.net

Bingo! That's the table. You can take a backup of this if needed (incase you need to investigate the messages further), but we're going to clear it out:

psql -U qradar -c "delete from sensorprotocolstatusentry where sps_id='10';"

DELETE 4

Taking a look at the log source in the log source management app, it is now in the 'OK' status with no messages!

Share