Custom SQL for message queue not working properly

This support forum board is for support questions relating to Nagios XI, our flagship commercial network monitoring solution.
Locked
WilliamLea1
Posts: 11
Joined: Fri Nov 05, 2021 11:00 am

Custom SQL for message queue not working properly

Post by WilliamLea1 »

Hi,
I want to implement a check that pulls messages from QSYSOPR into Nagios for current and previous day so it can be used for checking and reviewing the last 24 hours.

New to SQL so not sure if its written particularly well, however it works and shows the information I want. But when I put the custom SQL in Nagios and do a run check on the service it doesnt return anything on one system but on another it returns all messages not just whats on Qsysopr.
When checking the QZDASOINIT jobs they do have this error in them below:

Message . . . . : Procedure or user-defined function MESSAGE_QUEUE_INFO in
QSYS2 returned a warning SQLSTATE.
Cause . . . . . : An SQLSTATE of the form 01xxx was returned by the
procedure or user-defined function MESSAGE_QUEUE_INFO in QSYS2 (with
specific name QSQRCVMSGQ), along with message text SYSTEM LOG QUEUE IGNORED.
Recovery . . . : The user must understand the meaning of the warning. See
your database administrator, or the author of the user-defined function or
procedure.


The SQL which works locally fine but not through Nagios is:
SELECT MESSAGE_QUEUE_NAME, MESSAGE_TIMESTAMP, MESSAGE_TEXT
FROM QSYS2.MESSAGE_QUEUE_INFO WHERE MESSAGE_QUEUE_NAME
= 'QSYSOPR' AND DATE(MESSAGE_TIMESTAMP) = CURRENT DATE OR MESSAGE_QUEUE_NAME = 'QSYSOPR' AND DATE(MESSAGE_TIMESTAMP) = CURRENT DATE -1 DAY

Any ideas?

Cheers Will.
User avatar
mbellerue
Posts: 1403
Joined: Fri Jul 12, 2019 11:10 am

Re: Custom SQL for message queue not working properly

Post by mbellerue »

Hello,

To tackle the first issue, where the query doesn't seem to run properly, in Nagios or out of Nagios. Based on the error message, I would guess that MESSAGE_QUEUE_INFO is completely user defined, and for whatever reason, it was not defined on the server where you get that error message. That's more to do with the database side of things, and that's really beyond the scope of what I can get in to. But hopefully that gives you a thread to pull on.

But, you have a server where the query is executing properly on the host itself, but when run as a Nagios check, it returns more than it should.

Code: Select all

SELECT MESSAGE_QUEUE_NAME, MESSAGE_TIMESTAMP, MESSAGE_TEXT
FROM QSYS2.MESSAGE_QUEUE_INFO 
WHERE MESSAGE_QUEUE_NAME = 'QSYSOPR' AND DATE(MESSAGE_TIMESTAMP) = CURRENT DATE 
  OR MESSAGE_QUEUE_NAME = 'QSYSOPR' AND DATE(MESSAGE_TIMESTAMP) = CURRENT DATE -1 DAY
So this query looks like it's supposed to pull messages that were generated today or yesterday. Effectively giving you the last 24 hours worth of messages. Is that right?

The first thing that I can think to check is to make sure that the application you're using to query the database isn't limiting you to a certain subset of the data. You might try playing with ascending order to make sure that you're seeing the absolute oldest messages it will pull from your query.

The only other thing I can think of is that there's a character being commented out, or even added, when it's being run through Nagios XI. In the database, is it possible to see a history of queries run from a particular IP address? If it is, it would be good to verify that what's getting run is what we expect to be running.
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.

Be sure to check out our Knowledgebase for helpful articles and solutions!
User avatar
kfanselow
Posts: 241
Joined: Tue Aug 31, 2021 3:25 pm

Re: Custom SQL for message queue not working properly

Post by kfanselow »

Hi Will,

Not sure how far I can help with the specifics of the SQL query, but can you show us the output of a "Run Check Command" ?

Configure (top) -> Core Config Manager -> Services (left) ->
Find the service you've configured and click on the wrench on the right side.
Then Click on the "Run Check Command" button and the confirmation.

Thanks and Best Regards,
Keith
WilliamLea1
Posts: 11
Joined: Fri Nov 05, 2021 11:00 am

Re: Custom SQL for message queue not working properly

Post by WilliamLea1 »

Hi kfanselow/Mbellerue,

Thanks for the replies both.

"So this query looks like it's supposed to pull messages that were generated today or yesterday. Effectively giving you the last 24 hours worth of messages. Is that right?"
Yes it runs on the system fine and pulls up today and yesterday entries from a system managed file. So when I pass the exact same SQL statement to a Nagios service it pulls up whatever, from oldest to newest but obviously runs out of room as there will always be a lot of messages in that queue.. Just seems to ignore the date function part.
I could try and order it but when run on the system it only pulls up the right days so no need to. If I order it it would sort of solve the problem in that I would see the newest first but still I would imagine it would error with the amount of entries as its pulling everything?

Run check command:

[nagios@server.co.uk ~]$ /bin/bash /usr/local/nagios/libexec/check_ibmi_status.sh -M CustomSQL -SSL n -F QSYSOPRMSGQ -H 111.111.111.111 -W 9999 -C 9999
QSYSOPR MSGQ (current + previous) count of record: 1321
MESSAGE_QUEUE_NAME: QSYSOPR MESSAGE_TIMESTAMP: 2022-03-13 00:00:02.273589 MESSAGE_TEXT: Log version QHST22071A in QSYS closed and should be saved.
MESSAGE_QUEUE_NAME: QSYSOPR MESSAGE_TIMESTAMP: 2022-03-13 00:00:42.933461 MESSAGE_TEXT: Subsystem QINTER in library QSYS starting.
and so on.... But as you can see its dated the 13th.

Output from local system and as you can see it starts with 16th from 00:00.
QSYSOPR 2022-03-16-00.00.06.294465
QSYSOPR 2022-03-16-00.03.35.952968
QSYSOPR 2022-03-16-00.03.35.973058
QSYSOPR 2022-03-16-00.03.35.993114
QSYSOPR 2022-03-16-00.03.36.014014
QSYSOPR 2022-03-16-00.03.36.033387
QSYSOPR 2022-03-16-00.03.36.064552
and so on...... for about 170 messages, not the 1321 the run check command pulls out

Cheers Will.
WilliamLea1
Posts: 11
Joined: Fri Nov 05, 2021 11:00 am

Re: Custom SQL for message queue not working properly

Post by WilliamLea1 »

Hi guys,

Have you managed to have any thoughts on this yet?

Cheers Will.
gsmith
Posts: 1253
Joined: Tue Mar 02, 2021 11:15 am

Re: Custom SQL for message queue not working properly

Post by gsmith »

Hi

As a test I dumped a log file from a remote machine to a Nagios XI server using our NCPA agent
and was able to transfer 15591 lines and 1,377,784 characters.

I don't have an IBM machine running SQL at my disposal so I can't test using the IBM supplied
check_ibmi_status.sh plugin.

My suggestion is to install a SQL client on the Nagios XI server, and then query the IBM SQL machine
from the command line of the XI server using your query. If you receive the full output - 1321 messages - versus
the 170 messages - then that would indicate an issue/misconfiguration/limitation with the IBM plugin.

Please let me know the results.

Another question I have is what are you trying to monitor/measure? It seems like you are using Nagios XI to pull over
messages from yesterday and today to be analyzed. What is doing the analysis- a human or a script on the XI server?

Thanks
WilliamLea1
Posts: 11
Joined: Fri Nov 05, 2021 11:00 am

Re: Custom SQL for message queue not working properly

Post by WilliamLea1 »

Hi,

thanks for the reply. I will certainly look at that.

This is more for convenience. We have several systems and we do certain eyes on checks as a precaution. So if its possible to pull in the last 24 hours then it can be done for all systems in one place, Nagios. As opposed to logging onto several systems and performing the check there. So its not crucial, just easier option.

Cheers Will.
gsmith
Posts: 1253
Joined: Tue Mar 02, 2021 11:15 am

Re: Custom SQL for message queue not working properly

Post by gsmith »

Hi Will,

I can fully understand that.

I'm going to lock this topic since we have migrated to a new ticketing system, but definitely open
a ticket there if you have any more questions on this, or anything else.

Thanks

We're moving to a new support system!

The Nagios Answer Hub is a place where you can get help with technical questions from our experts. There, you can quickly open tickets and join discussion boards.

Request Nagios Answer Hub access here:
https://info.nagios.com/answer-hub-access-new-users

After completing the access form, you will be given access to a portal where new tickets can be created. We will keep the old customer forum sections and ticket system available for current cases to be resolved.
Locked