[Please Help] Walkthrough for a SimpleScript Query

[anthonyl]

I’m trying to create a Script Query to graph the number of financial deals in a position keeping system. Basically, the output is a count of the rows in tables that contain these deals. I’ve searched the forum and haven’t found anything in the form of a walkthrough for script queries, so I thought – in addition to seeking help – this could become one.

Cacti: Ver: 0.8.6c
OS: SPARC Solaris 2.8

Here are the steps I’ve followed, loosely based on the thread from Lux “HOW-TO add new MIB Variables to Cacti”. That thread is for an SNMP Query, but it’s the closest I could find.

1. I create a script_query xml file. In this case, I call it kondor_deals.xml

# cat kondor_deals.xml
<interface>
<name>Get the deal types and deals in the kondor+ database</name>
<description>Queries the Kondor+ database for deal numbers</description>
<script_path>perl |path_cacti|/scripts/query_kondor_deals.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>dealType</index_order>
<index_order_type>alphabetic</index_order_type>
<index_title_format>|chosen_order_field|</index_title_format>
<fields>
<dealType>
<name>Username</name>
<direction>input</direction>
<query_name>username</query_name>
</dealType>
<dealType>
<name>Password</name>
<direction>input</direction>
<query_name>password</query_name>
</dealType>
<dealType>
<name>Database Name</name>
<direction>input</direction>
<query_name>dbname</query_name>
</dealType>
<dealType>
<name>Deal Type</name>
<direction>output</direction>
<query_name>dealtype</query_name>
</dealType>
<countDeals>
<name>Count Deals</name>
<direction>output</direction>
<query_name>countdeals</query_name>
</countDeals>
</fields>
</interface>

Note that this query requires the username, password and database name in order to retrieve the data. I’ve defined these as <direction>input</direction>.

2. Next I need to create the script that retrieves the data. In this case, query_kondor_deals.pl. I’ll spare the space by not listing the perl script, but the output is relevant, so I’ll show it. The script needs to respond to 2 arguments(index or get), and requires 3 arguments to address the appropriate database (username, password, database name).
a. So, ./query_kondor_deals.pl username password dbname index returns:
SwapDeals
BondsDeals
LinkedDeals
LoansDepositDeals

…
Where username, password and dbname are actual.

and, ./query_kondor_deals.pl username password dbname get BondsDeals returns:
BondsDeals:0019

3. Now I need to tell Cacti about this query. Cacti-Data QueriesAdd
a. [Data Queries New]
b. Name: Kondor - Deal Count
c. Description: Query the database to retrieve deal count
d. XML Path: <path_cacti>/resource/script_queries/kondor_deals.xml
e. Data Input Method: Get Script Data (Indexed)
f. Click Create
g. Check to see if the XML file was found by cacti – and click Save.
4. Next I create the Data Template. CactiData TemplatesAdd
a. [Data Templates]
b. Name: Kondor Deal Count
c. [Data Source]
d. Name: |hostname| - deal count
e. Data Input Method: Get Script Data (Indexed)
f. [Data Source Item]
g. Internal Data Source Name: dealcount
h. Min value: 0
i. Max value: 9999 # This can get pretty big
j. Data Source Type: GAUGE
k. Heartbeat: 600
l. Click Create – Opens [Custom Data]
m. [Custom Data]
n. Put a check in each box for Index Type, Index Value, Output Type ID
5. Time to create a Graph Template CactiGraph Templates Add
a. [Template]
b. Name: Kondor Deal Count
c. [Graph Template]
d. Title |hostname| Kondor Deal Count
e. Change Upper Limit to 9999
f. Click Create – Saves the graph and lets us define Graph Template Items, Input Items, etc.
g. [Graph Template Items]  Add
h. Data Source: Kondor Deal Count – (dealcount) # This comes from the Script Query I defined
i. Color, A nice blue. I like 4668E4 
j. Graph Item Type, LINE 3. A Thick Line
k. Consolidation, Average
l. CDEF None
m. Everything else stays as is.
n. Click Create – Automatically creates Graph Input Items, and includes the appropriate data source.
o. Save the graph templates.
6. Now I need to create a host template to associate the graph with. Please someone correct me here, but I think if I create a host template, associate data queries with it, and make devices using it, it will create graphs for me? Anyway, I proceed as if that’s the case! Cacti Host Templates Add
7. Name: Kondor Host
8. Click Save
9. Under Associated Data Queries, we add ours: Kondor – Deal Count
10. Click Save
11. Now I need to pick a host (device) to implement the Host Template I created. CactiDevices
a. I’ve already got some defined, so I pick one that’s appropriate
b. Host Template: Kondor Host
c. Click Save
d. It reloads the page, and shows the associated data query, Kondor – Deal Count. The status says Success [0 items, 0 Rows].


Here’s where my problem comes in, and where maybe we can finish this walkthough: I click on (Verbose Query), which returns:

+ Running data query [12].
+ Found type = '4 '[script query].
+ Found data query XML file at + XML file parsed ok.
+ Executing script for list of indexes 'perl /files/www/public_html/cacti/scripts/query_kondor_deals.pl index' …

It’s not retrieving any data, because It hasn’t passed the input’s. I’m not surprised, as I haven’t defined them  -- But what step am I missing?

I really appreciate and look forward to your comments.

–Anthony

[ragez]

Ok,

when cacti doc say <direction>input<direction> this is a bit fuzzy, in fact the only input is the index .... the other input field are here just for to make the interface more convenient

if you want to pass some argument to your query script you have to had this line to xml file

<arg_prepend>|host_hostname|</arg_prepend>

and your script will be called like that

./my_query_script hostname get field_name index

in your case i think you cannot use cacti variable (or you must create some device with the name of your database)

i think if don't have many user/passwd/database combinaison you can make one query by combinaison ....

RaGeZ

[deanpence]

This doesn't appear to solve the problem the original poster was (and I am) having trouble with. In particular, what is the precise order in which you add elements to cacti (particularly later in the process) after creating a data query? And what are appropriate values for some of the less descriptively-titled input fields like "Input Type", "Input Value", and "Output Type ID"?

I created a data query (using script/command), a data template (leaving the aforementioned fields all checked and overridden by data sources), a graph template, a host template, and finally a device, creating a graph from the associated templates, but in the final steps, it is difficult to know whether I've done everything correctly so far.

Is there any actual documentation on this process? Is anyone willing to help with the process? I've had very bad luck in the forums so far; no one seems willing to help fill in the huge blanks left by the documentation. I'd appreciate any help anyone could give.

[hans_olo]

Hello,

I am experiencing the same or similar problem which I can not solve by reading the docs.

I have a script that wants some arguments like host, port, database, user, passwort and so on. At the moment it wants this in option form: --database database --host host etc., but I can rewrite it to accept the parameters without name.

How is this reflected in the XML-file actually, can someone writeup a sample one with more than only one parameter?

My task is to get performance parameters out of oracle databases and visualizing them. I use the instant_client and some perl-code to send SQL-commands, as our databases do not talk SNMP. If I manage to get this running, I will contribute it, of course.
...
But I need a little help with the XML

Best regards,

Stefan

[gandalf]

Please see my Cacti Stuff. It's covered in one of the last chapters
Reinhard

[Leonardo Helman]

Hi, I still can't find an answer to this.

Is it possible to have a data query, non snmp, script with custom parameters, without using the snmp definition in the device?

Something like

Code: Select all

path/myscript customarg1 customarg2 ... index/get/query...

where customargN is not in the variable.php function substitute_host_data



In the latest docs that I found (http://docs.cacti.net/node/502)
it isn't even the prepend thing.
But in earlier howtos (http://docs.cacti.net/node/294)
there is something like:

Code: Select all

 <arg_prepend>|host_hostname| |host_snmp_community| |host_snmp_version| |host_snmp_port| |host_snmp_timeout| "|host_snmp_username|" "|host_snmp_password|"</arg_prepend>

Is there a way to specify something not snmp related?

Something like |my_password| so I could specify "SNMP VERSION -> not in use" or use the snmp session of that device for just snmp


Thanks in advance
Saludos
Leonardo Helman

[gandalf]

You may add either "cacti internal variables" like |host_hostname| or a string constant. Currently, it is NOT possible to query the user for such a parameter at graph generation time
Reinhard