Script Data Query Problems

[ghost_o]

OK - I followed to the best of my knowledge the query_unix_partitions.pl script and xml to create mine. I am querying a postgres database for my metrics I want to graph. The perl script is successful from the command line, and the xml

Code: Select all

<interface>
        <name>Get RT Queues</name>
        <description>Queries a list of Queues in Request Tracker.</description>
        <script_path>perl |path_cacti|/scripts/query_rt.pl</script_path>
        <arg_index>index</arg_index>
        <arg_query>query</arg_query>
        <arg_get>get</arg_get>
        <arg_num_indexes>num_indexes</arg_num_indexes>
        <output_delimeter>:</output_delimeter>
        <index_order>Queue</index_order>
        <index_order_type>alphabetic</index_order_type>
        <index_title_format>|chosen_order_field|</index_title_format>

        <fields>
                <Queue>
                        <name>Queue Name</name>
                        <direction>input</direction>
                        <query_name>queue</query_name>
                </Queue>

                <Total>
                        <name>Total Open</name>
                        <direction>output</direction>
                        <query_name>total</query_name>
                </Total>

                <Average>
                        <name>Average</name>
                        <direction>output</direction>
                        <query_name>average</query_name>
                </Average>

                <Closed>
                        <name>Closed</name>
                        <direction>output</direction>
                        <query_name>closed</query_name>
                </Closed>

                <Open>
                        <name>Open</name>
                        <direction>output</direction>
                        <query_name>open</query_name>
                </Open>

                <New>
                        <name>New</name>
                        <direction>output</direction>
                        <query_name>new</query_name>
                </New>
        </fields>
</interface>

example output:

Code: Select all

*[root@ox]* ./query_rt.pl index
all_logging
___Approvals
avnet-sales
BCBS
bellcanada-sales
bonnetcreek
brazosportisd
bugs
burlington
CCG
cendant
Cisco
cisco-alerts
CityofDenton
collincounty
comstor-orders
comstor-sales
comstor-uk
cs_followup
current_installations
debug

Code: Select all

*[root@ox]* ./query_rt.pl query
bugs:
Cisco:
feature-request:
partner:
post_installation:
quotes:
sales:
sales-support:
Support:

Code: Select all

*[root@ox]* ./query_rt.pl query total
bugs:107
Cisco:9
feature-request:160
partner:34
post_installation:12
quotes:303
sales:51
sales-support:26
Support:458

Code: Select all

*[root@ox]* ./query_rt.pl get total Support
458

So I am assuming everything from that side of the fence is good..

But, the poller is returning:

Code: Select all

CACTID: Host[14] ERROR: Empty result [rt.cistera.com]: 'perl /var/www/cacti.corp.dallas.cistera.com/htdocs/scripts/query_rt.pl  get average bugs'
CACTID: Host[14] DS[67] WARNING: Result from SCRIPT not valid. Partial Result: ...

Yet when I run the same:

Code: Select all

*[root@ox]* perl /var/www/cacti.corp.dallas.cistera.com/htdocs/scripts/query_rt.pl  get average bugs
167

It can't be a permissions problem, or I would not be this far.

I can also see from the poller log, it is putting each rrd into an inappropriate file, so I will have to figure out that as well - it is storing to <hostname>_closed (which is an output of the script) for each queue I want... Not sure where to fix that, or if I am missing something fundamental.

I have read lvm's (thank you btw) excellent writeups on snmp data queries and data templates, etc all day and am still coming up dry.

TIA for any help

-Greg

[ghost_o]

OK - changing from cactid to cmd.php fixed the 1st issue and I can see the data being populated into the rrd files, but I still do not know how to get the rrd data into hostname-<queue> vs hostname-<status> files so they can be unique to hosts..

I am sure it is misconfig, but I see no examples of script query input methods, or templates and the docs are a bit hard to follow..

Thanks,

Greg

[gandalf]

When using the latest CACTID, you must provide /full/path/to/perl in the XML description
Reinhard

[ghost_o]

Thanks for the reply, but unfortunately putting the full path, changing back to cactid and flushing the queue produced the previous results.

Running cmd.php is OK - I am only monitoring ~200 devices so far, so no biggie - although I would like the huge speed improvement.

I guess if you don't mind, let me explain in more detail what I have. I am querying a Request Tracker database we use for our ticketing system. My top posted XML will show you I have:

1 input - queue
1 output - new
1 output - open
1 output - total
1 output - closed
1 output - average

OK - after days of twiddling with this, can you please explain something.

My data template uses |host_description| and |query_queue| (only things uniqie from host and query). Is query_queue (the only input) invalid? When I browse my data sources, teh host_description is translated correctly, but the |query_queue| shows as typed. This causes all of my RRD files to go to:

/htdocs/rra/request_tracker_closed_XX.rrd instead of:

/htdocs/rra/request_tracker_|query_queue|_XX.rrd

Does the data template have to be distint on an output from the xml file? Everything I read says not, but I cannot get it to play nice. all queues are sent to "closed" rrd files which makes no sense to me,

I will post screenshots of everything if interested.

Thanks again,

Greg

[gandalf]

Please add the XML and the script as well
Reinhard

[ghost_o]

Here ya go...

Code: Select all

*[root@ox]* cat rt.xml
<interface>
        <name>Get RT Queues</name>
        <description>Queries a list of Queues in Request Tracker.</description>
        <script_path>/usr/bin/perl |path_cacti|/scripts/query_rt.pl</script_path>
        <arg_index>index</arg_index>
        <arg_query>query</arg_query>
        <arg_get>get</arg_get>
        <arg_num_indexes>num_indexes</arg_num_indexes>
        <output_delimeter>:</output_delimeter>
        <index_order>Queue</index_order>
        <index_order_type>alphabetic</index_order_type>
        <index_title_format>|chosen_order_field|</index_title_format>

        <fields>
                <queue>
                        <name>Queue Name</name>
                        <direction>input</direction>
                        <query_name>queue</query_name>
                </queue>

                <total>
                        <name>Total Open</name>
                        <direction>output</direction>
                        <query_name>total</query_name>
                </total>

                <average>
                        <name>Average</name>
                        <direction>output</direction>
                        <query_name>average</query_name>
                </average>

                <closed>
                        <name>Closed</name>
                        <direction>output</direction>
                        <query_name>closed</query_name>
                </closed>

                <open>
                        <name>Open</name>
                        <direction>output</direction>
                        <query_name>open</query_name>
                </open>

                <new>
                        <name>New</name>
                        <direction>output</direction>
                        <query_name>new</query_name>
                </new>
        </fields>
</interface>

And the script:

Code: Select all

#!/usr/bin/perl
#
use DBI;
use DBD::Pg;


my $db = "rt3";
my $db_server = "xxxxxxx";
my $db_user = "xxxxxxx";
my $db_pass = "xxxxxxx";


my $dbh = DBI->connect( "dbi:Pg:dbname=$db;host=$db_server", $db_user, $db_pass );

if(( $ARGV[0] eq "index" ) || ( $ARGV[0] eq "get" ) || ( $ARGV[0] eq "query" )) {
        $sql = "SELECT DISTINCT id, name from queues ORDER BY name";
        $sth = $dbh->prepare( $sql );
        $sth->execute();
        while( my( $queue_id, $queue_name ) = $sth->fetchrow() ) {
                if( $ARGV[0] eq "index" ) {
                        print "$queue_name\n";

                } if( $ARGV[0] eq "get" || $ARGV[0] eq "query" ) {
                        $sql_open = "SELECT COUNT (*) FROM tickets WHERE queue = ? and status = ?";
                        $open_tickets = $dbh->prepare( $sql_open );
                        $open_tickets->execute( $queue_id,'open' );
                        while( my $result_open = $open_tickets->fetchrow() ) {
                                chomp( $result_open );
                                $sql_closed = "SELECT COUNT (*) FROM tickets WHERE queue = ? and status = ?";
                                $closed_tickets = $dbh->prepare( $sql_closed );
                                $closed_tickets->execute( $queue_id, 'resolved' );
                                while( my $result_closed = $closed_tickets->fetchrow() ) {
                                        chomp( $result_closed );
                                        $sql_new = "SELECT COUNT (*) FROM tickets WHERE queue = ? and status = ?";
                                        $new_tickets = $dbh->prepare( $sql_new );
                                        $new_tickets->execute( $queue_id, 'new' );
                                        while( my $result_new = $new_tickets->fetchrow() ) {
                                                chomp( $result_new );
                                                $sql_average = "SELECT SUM(EXTRACT(EPOCH FROM tickets.lastupdated - tickets.created)) where
                                                tickets.queue = ? and tickets.status = ?";
                                                $average_tickets = $dbh->prepare( $sql_average );
                                                $average_tickets->execute( $queue_id, 'resolved' );
                                                while( my $result_average = $average_tickets->fetchrow() ) {
                                                        chomp( $result_average );
                                                        $average = ($result_average / $result_closed / 86400 );                                                        $average_nice = sprintf("%.0f", $average);
                                                        $total = ($result_new + $result_closed + $result_open);                                                        %output = (
                                                                queue           => $queue_name,
                                                                open            => $result_open,
                                                                closed          => $result_closed,
                                                                average         => $average_nice,
                                                                new                     => $result_new,
                                                                total           => $total
                                                        );

                                                        if(( $ARGV[0] eq "get" ) && ($ARGV[2] eq $queue_name )) {
                                                                print $output{$ARGV[1]};
                                                        } elsif( $ARGV[0] eq "query" ) {
                                                                print "$output{queue}:$output{$ARGV[1]}\n";
                                                        }
                                                }
                                                $average_tickets->finish();
                                        }
                                        $new_tickets->finish();
                                }
                                $closed_tickets->finish();
                        }
                        $open_tickets->finish();
                }
        }
}
$dbh->disconnect();

Thanks for any insight!

[ghost_o]

Here are some screenshots...

[gandalf]

Hmm, no clue yet. Would you please post a copy of the results from Verbose Query?
Reinhard

[ghost_o]

Here ya go - thanks again!

Code: Select all

+ Running data query [12].
+ Found type = '4 '[script query].
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ XML file parsed ok.
+ Executing script for list of indexes '/usr/bin/perl /var/www/cacti.corp.dallas.cistera.com/htdocs/scripts/query_rt.pl index'
+ Executing script query '/usr/bin/perl /var/www/cacti.corp.dallas.cistera.com/htdocs/scripts/query_rt.pl query queue'
+ Found item [queue='bugs'] index: bugs
+ Found item [queue='Cisco'] index: Cisco
+ Found item [queue='feature-request'] index: feature-request
+ Found item [queue='info'] index: info
+ Found item [queue='partner'] index: partner
+ Found item [queue='post_installation'] index: post_installation
+ Found item [queue='quotes'] index: quotes
+ Found item [queue='sales'] index: sales
+ Found item [queue='sales-support'] index: sales-support
+ Found item [queue='Support'] index: Support
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'
+ Found data query XML file at '/var/www/cacti.corp.dallas.cistera.com/htdocs/resource/script_queries/rt.xml'

[gandalf]

So this one looks rather good. All indices are found, aren't they? Lets proceed to the next step. Please post a screenshot of the next page, when clicking Create Graphs for this host (please focus to this Associated Data Query). Please select an arbitrary index item next and create. Please send the text generated from Create as well
Reinhard

[ghost_o]

Yeah - everything up until then was good. I have been swamped and made so many changes to try to get it to work that I am gonna start from scratch on the cacti config portion (not perl and xml).

I will post along my steps as I go when I get back to it tonight or this afternoon.

Thanks,

Greg