SQL Query returns "-1", how ot increase the runtime

[dlukinski]

Hello XI support

SQL Query returns "-1", how ot increase the runtime (for how long this query will run and Nagios would wait for the results?)

Thank you,
Dimitri

[scottwilkerson]

What command are you running?

[dlukinski]

What command are you running?

--username "user" --password "pass" --database SOMEprodfi --port 1439 --query "select+++++++++++++assoc.id++++++++++++++++++++as+id%2C+++++++++++++parentNode.id+++++++++++++++as+parentNodeId%2C+++++++++++++parentNode.version++++++++++as+parentNodeVersion%2C+++++++++++++parentStore.protocol++++++++as+parentNodeProtocol%2C+++++++++++++parentStore.identifier++++++as+parentNodeIdentifier%2C+++++++++++++parentNode.uuid+++++++++++++as+parentNodeUuid%2C+++++++++++++childNode.id++++++++++++++++as+childNodeId%2C+++++++++++++childNode.version+++++++++++as+childNodeVersion%2C+++++++++++++childStore.protocol+++++++++as+childNodeProtocol%2C+++++++++++++childStore.identifier+++++++as+childNodeIdentifier%2C+++++++++++++childNode.uuid++++++++++++++as+childNodeUuid%2C+++++++++++++assoc.type_qname_id+++++++++as+type_qname_id%2C+++++++++++++assoc.child_node_name_crc+++as+child_node_name_crc%2C+++++++++++++assoc.child_node_name+++++++as+child_node_name%2C+++++++++++++assoc.qname_ns_id+++++++++++as+qname_ns_id%2C+++++++++++++assoc.qname_localname+++++++as+qname_localname%2C+++++++++++++assoc.is_primary++++++++++++as+is_primary%2C+++++++++++++assoc.assoc_index+++++++++++as+assoc_index+++++++++++++++++++++++++from+++++++++++++alf_child_assoc+assoc+++++++++++++join+alf_node+parentNode+on+%28parentNode.id+%3D+assoc.parent_node_id%29+++++++++++++join+alf_store+parentStore+on+%28parentStore.id+%3D+parentNode.store_id%29+++++++++++++join+alf_node+childNode+on+%28childNode.id+%3D+assoc.child_node_id%29+++++++++++++left+join+alf_store+childStore+on+%28childStore.id+%3D+childNode.store_id%29+++++++++++++++where+++++++++++++parentNode.id+%3D+464+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++and+childNode.type_qname_id+in+++++++++++++++++++%28+++++++++++++++++++++++54++++++++++++++++++%29++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++order+by+++++++++++++assoc.assoc_index+ASC%2C+++++++++++++assoc.id+ASC" --decode --warning 50 --critical 200 --querywarning 10 --querycritical 100 --result "Expected result"

[scottwilkerson]

Can you supply the full command so we know what plugin you are using?

[dlukinski]

Can you supply the full command so we know what plugin you are using?

Nagios XI stock MsSQL Query plugin

Here is the command:

Code: Select all

select             assoc.id                    as id,             parentNode.id               as parentNodeId,             parentNode.version          as parentNodeVersion,             parentStore.protocol        as parentNodeProtocol,             parentStore.identifier      as parentNodeIdentifier,             parentNode.uuid             as parentNodeUuid,             childNode.id                as childNodeId,             childNode.version           as childNodeVersion,             childStore.protocol         as childNodeProtocol,             childStore.identifier       as childNodeIdentifier,             childNode.uuid              as childNodeUuid,             assoc.type_qname_id         as type_qname_id,             assoc.child_node_name_crc   as child_node_name_crc,             assoc.child_node_name       as child_node_name,             assoc.qname_ns_id           as qname_ns_id,             assoc.qname_localname       as qname_localname,             assoc.is_primary            as is_primary,             assoc.assoc_index           as assoc_index                         from             alf_child_assoc assoc             join alf_node parentNode on (parentNode.id = assoc.parent_node_id)             join alf_store parentStore on (parentStore.id = parentNode.store_id)             join alf_node childNode on (childNode.id = assoc.child_node_id)             left join alf_store childStore on (childStore.id = childNode.store_id)               where             parentNode.id = 464                                                                                                                                                                                         and childNode.type_qname_id in                   (                       54                  )                                                                    order by             assoc.assoc_index ASC,             assoc.id ASC

[scottwilkerson]

What do you get running this from the command line (replacing HOST/USER/PASS and DATABASE)

Code: Select all

    /usr/local/nagios/libexec/check_mssql -H HOST --username USER --password "PASS" --database DATABASE --port 1439 --query "select%20assoc.id%20as%20id%2C%20parentNode.id%20as%20parentNodeId%2C%20parentNode.version%20as%20parentNodeVersion%2C%20parentStore.protocol%20as%20parentNodeProtocol%2C%20parentStore.identifier%20as%20parentNodeIdentifier%2C%20parentNode.uuid%20as%20parentNodeUuid%2C%20childNode.id%20as%20childNodeId%2C%20childNode.version%20as%20childNodeVersion%2C%20childStore.protocol%20as%20childNodeProtocol%2C%20childStore.identifier%20as%20childNodeIdentifier%2C%20childNode.uuid%20as%20childNodeUuid%2C%20assoc.type_qname_id%20as%20type_qname_id%2C%20assoc.child_node_name_crc%20as%20child_node_name_crc%2C%20assoc.child_node_name%20as%20child_node_name%2C%20assoc.qname_ns_id%20as%20qname_ns_id%2C%20assoc.qname_localname%20as%20qname_localname%2C%20assoc.is_primary%20as%20is_primary%2C%20assoc.assoc_index%20as%20assoc_index%20from%20alf_child_assoc%20assoc%20join%20alf_node%20parentNode%20on%20%28parentNode.id%20%3D%20assoc.parent_node_id%29%20join%20alf_store%20parentStore%20on%20%28parentStore.id%20%3D%20parentNode.store_id%29%20join%20alf_node%20childNode%20on%20%28childNode.id%20%3D%20assoc.child_node_id%29%20left%20join%20alf_store%20childStore%20on%20%28childStore.id%20%3D%20childNode.store_id%29%20where%20parentNode.id%20%3D%20464%20%20%20and%20childNode.type_qname_id%20in%20%28%2054%20%29%20%20order%20by%20assoc.assoc_index%20ASC%2C%20assoc.id%20ASC%0A" --decode --warning 50 --critical 200 --querywarning 10 --querycritical 100 --result "Expected result"

[dlukinski]

What do you get running this from the command line (replacing HOST/USER/PASS and DATABASE)

Code: Select all

    /usr/local/nagios/libexec/check_mssql -H HOST --username USER --password "PASS" --database DATABASE --port 1439 --query "select%20assoc.id%20as%20id%2C%20parentNode.id%20as%20parentNodeId%2C%20parentNode.version%20as%20parentNodeVersion%2C%20parentStore.protocol%20as%20parentNodeProtocol%2C%20parentStore.identifier%20as%20parentNodeIdentifier%2C%20parentNode.uuid%20as%20parentNodeUuid%2C%20childNode.id%20as%20childNodeId%2C%20childNode.version%20as%20childNodeVersion%2C%20childStore.protocol%20as%20childNodeProtocol%2C%20childStore.identifier%20as%20childNodeIdentifier%2C%20childNode.uuid%20as%20childNodeUuid%2C%20assoc.type_qname_id%20as%20type_qname_id%2C%20assoc.child_node_name_crc%20as%20child_node_name_crc%2C%20assoc.child_node_name%20as%20child_node_name%2C%20assoc.qname_ns_id%20as%20qname_ns_id%2C%20assoc.qname_localname%20as%20qname_localname%2C%20assoc.is_primary%20as%20is_primary%2C%20assoc.assoc_index%20as%20assoc_index%20from%20alf_child_assoc%20assoc%20join%20alf_node%20parentNode%20on%20%28parentNode.id%20%3D%20assoc.parent_node_id%29%20join%20alf_store%20parentStore%20on%20%28parentStore.id%20%3D%20parentNode.store_id%29%20join%20alf_node%20childNode%20on%20%28childNode.id%20%3D%20assoc.child_node_id%29%20left%20join%20alf_store%20childStore%20on%20%28childStore.id%20%3D%20childNode.store_id%29%20where%20parentNode.id%20%3D%20464%20%20%20and%20childNode.type_qname_id%20in%20%28%2054%20%29%20%20order%20by%20assoc.assoc_index%20ASC%2C%20assoc.id%20ASC%0A" --decode --warning 50 --critical 200 --querywarning 10 --querycritical 100 --result "Expected result"

Same result:

login as: root
[email protected]'s password:

[root@nagxiprod02 ~]# /usr/local/nagios/libexec/check_mssql -H fikc-clsql01s6.res.kcg.global --username nagiosxi --password "checkNAG1" --database alfarcprodfi --port 1439 --query "select%20assoc.id%20as%20id%2C%20parentNode.id%20as%20parentNodeId%2C%20parentNode.version%20as%20parentNodeVersion%2C%20parentStore.protocol%20as%20parentNodeProtocol%2C%20parentStore.identifier%20as%20parentNodeIdentifier%2C%20parentNode.uuid%20as%20parentNodeUuid%2C%20childNode.id%20as%20childNodeId%2C%20childNode.version%20as%20childNodeVersion%2C%20childStore.protocol%20as%20childNodeProtocol%2C%20childStore.identifier%20as%20childNodeIdentifier%2C%20childNode.uuid%20as%20childNodeUuid%2C%20assoc.type_qname_id%20as%20type_qname_id%2C%20assoc.child_node_name_crc%20as%20child_node_name_crc%2C%20assoc.child_node_name%20as%20child_node_name%2C%20assoc.qname_ns_id%20as%20qname_ns_id%2C%20assoc.qname_localname%20as%20qname_localname%2C%20assoc.is_primary%20as%20is_primary%2C%20assoc.assoc_index%20as%20assoc_index%20from%20alf_child_assoc%20assoc%20join%20alf_node%20parentNode%20on%20%28parentNode.id%20%3D%20assoc.parent_node_id%29%20join%20alf_store%20parentStore%20on%20%28parentStore.id%20%3D%20parentNode.store_id%29%20join%20alf_node%20childNode%20on%20%28childNode.id%20%3D%20assoc.child_node_id%29%20left%20join%20alf_store%20childStore%20on%20%28childStore.id%20%3D%20childNode.store_id%29%20where%20parentNode.id%20%3D%20464%20%20%20and%20childNode.type_qname_id%20in%20%28%2054%20%29%20%20order%20by%20assoc.assoc_index%20ASC%2C%20assoc.id%20ASC%0A" --decode --warning 50 --critical 200 --querywarning 10 --querycritical 100 --result "Expected result"
CRITICAL: Query result -1 was higher than Query critical threshold 100.|query_duration=0.019526s;50;200 'assoc_index'=-1;10;100
[root@nagxiprod02 ~]#

[dlukinski]

This query executes successfully directly from SQL server:

98264725 464 6 workspace SpacesStore 52f905fd-6876-4230-9ad9-92ad4e0da8b6 101155067 6 workspace SpacesStore 86527932-9edf-499a-95e1-6780e8bd88b5 36 3953709031 sample.msg 7 Sample.msg 1 -1

Maybe Nagios only picks the last number?

[scottwilkerson]

Yes, what are you looking to target for an OK state??