XMA: How to see messages that are sent by the SLSENDER using SQL queries.

XMA: How to see messages that are sent by the SLSENDER using SQL queries.

To get the host names and port numbers with failing messages, and number of messages waiting in queue to be sent: 
  SELECT TARGET_HOST, TARGET_PORT, COUNT(*)  FROM =XMA_SYSLOGQ              WHERE ALERTEDGMT = TIMESTAMP "1970-01-01:00:00:00.000000"    GROUP BY TARGET_HOST, TARGET_PORT                             BROWSE ACCESS;

Replacing the = by > will return the counts of successfully sent messages by destination. 

To delete not sent messages for a given destination: 
 SQLCI> DELETE FROM =XMA_SYSLOGQ   WHERE ALERTEDGMT = TIMESTAMP "1970-01-01:00:00:00.000000"    AND TARGET_HOST = "192.168.1.123"     AND TARGET_PORT = 514;
 
  1. To forward not sent messages from a given destination to a different one:
 SQLCI> UPDATE =XMA_SYSLOGQ SET TARGET_HOST = "192.168.1.321",                         TARGET_PORT = 1514 WHERE ALERTEDGMT = TIMESTAMP "1970-01-01:00:00:00.000000"       AND TARGET_HOST = "192.168.1.123"    AND TARGET_PORT = 514;

To check throughput: Returns activity number from midnight of current day through the current time: 
  SQLCI> SELECT   MIN(entrygmt)  , MAX(entrygmt)        , MIN(alertedgmt), MAX(alertedgmt)        , ENTRYGMT hour         , ((JULIANTIMESTAMP(ENTRYGMT)/  60000000)          -(JULIANTIMESTAMP(ENTRYGMT)/3600000000)*60)/5        , COUNT(*) FROM =XMA_SYSLOGQ WHERE (PARTITIONKEY, ENTRYGMT, PART)  >(2,(CURRENT YEAR TO DAY) YEAR TO FRACTION(6),0) GROUP BY 5, 6 BROWSE ACCESS;
Each record represents a 5 minute slice of time and contains the following columns:
  1. Timestamp of the first record?s insert into the SYSLOGQ table
  2. Timestamp of the last record?s insert into the SYSLOGQ table
  3. Timestamp of first record?s send
  4. Timestamp of last record?s send
  5. Hour ? can be ignored, it?s there for grouping of the records only
  6. Number of 5 minute slice within the hour  ? can be ignored
  7. Number of records inserted into the SYSLOGQ table

Returns activity number for any time window (change timestamps as desired): 
  SQLCI> SELECT   MIN(entrygmt),  MAX(entrygmt)        , MIN(alertedgmt),MAX(alertedgmt)        , ENTRYGMT hour         , ((JULIANTIMESTAMP(ENTRYGMT)/  60000000)          -(JULIANTIMESTAMP(ENTRYGMT)/3600000000)*60)/5        , COUNT(*) FROM =XMA_SYSLOGQ WHERE (PARTITIONKEY, ENTRYGMT, PART) BETWEEN (2,(timestamp "2013-06-15:12:00:00.000000") YEAR TO FRACTION(6),0) AND  (2,(timestamp "2013-06-17:23:59:59.999999") YEAR TO FRACTION(6),0)    GROUP BY 5, 6 BROWSE ACCESS;
 
Each record represents a 5 minute slice of time and contains the following columns:
  1. Timestamp of the first record?s insert into the SYSLOGQ table
  2. Timestamp of the last record?s insert into the SYSLOGQ table
  3. Timestamp of first record?s send
  4. Timestamp of last record?s send
  5. Hour ? can be ignored, it?s there for grouping of the records only
  6. Number of 5 minute slice within the hour  ? can be ignored
  7. Number of records inserted into the SYSLOGQ table

If this article did not provide a solution, please open a case with our support team by sending an email message to support@xypro.com.

    • Related Articles

    • XMA: Critical Msg:Syslog Sender SLSENDER Unable to send messages to host

      Wait for the HKEEPER to delete the records based on the maximum age of not sent messages to be retained or use the following SQL queries to identify and delete or change the relevant records. Show ?stuck? records and allow them to be deleted or ...

    • XMA: SLSENDER Error unable to send messages to host.

      The SYSLOGQ database table in the XMADAT subvolume has 2 columns that can be used to determine if messages are being sent to the SIEM. ENTRYGMT is the timestamp when the message was inserted into the queue by a mover. ALERTEDGMT is the timestamp when ...

    • XMA: SLSENDER fails to start after upgrading to Merged Audit 2.65

      There is a known issue where SLSENDER will not start after upgrading to XMA 2.65. If the customer cannot start SLSENDER after upgrading, the post upgrade steps below can be used. If we know a customer will be upgrading to XMA 2.65, pre-upgrade steps ...

    • XMA: How to get SQL queries in SQLXPress audit passed to the XMA database via SQX mover

      The Audit Level in SQLXPress must be set to ACCESS for SQL queries and CONTROL statements to be added to SQLXPress audit. Once the queries are logged the SQLXPress audit, XMA reads SQLXPress audit and adds queries to the XMA database. The steps to ...

    • XMA: How to uninstall SLSENDER.

      As the SLSENDER is configured and added to the XMA pathway when the customer follows the installation process in the XMA manual. The following procedure can be used to manually stop the SLSENDER , remove any records that have been generated, and stop ...