How Do You Use DSStats

[gsaray101]

I install this plugin, how does this work?

[TheWitness]

DSStats collects data into several tables:

Transient Tables
data_source_statistics_hourly_cache
data_source_statistics_hourly_last

Permanent Statistical Tables:
data_source_statistics_hourly
data_source_statistics_daily
data_source_statistics_weekly
data_source_statistics_monthly
data_source_statistics_yearly

The hourly cache and the last tables are memory tables. The hourly cache can bet big, depending on the number of data sources. The last table contains prior measurements of data for counters and derive type RRDfile data sources types.

If you want to get the last sampled data for anything:

Code: Select all

SELECT value FROM data_source_statistics_hourly_cache 
WHERE local_data_id=??? AND rrd_name=??? 
ORDER BY time DESC 
LIMIT 1;

If you want to get the max and average for the last hour:

Code: Select all

SELECT average, peak FROM data_source_statistics_hourly 
WHERE local_data_id=??? AND rrd_name=???

If you want to get the top ten "traffic_in" for the last hour:

Code: Select all

SELECT average, peak FROM data_source_statistics_hourly
WHERE rrd_name='traffic_in'
ORDER BY average DESC
LIMIT 10;

As I mentioned in the release notes, this plugin is really an INFRASTRUCTURE plugin, so if you are not a Plugin Developer, it will serve no purpose but to confuse you.

TheWitness

[henkwiedig]

If you want to get the top ten "traffic_in" for the last hour:

Code: Select all

SELECT average, peak FROM data_source_statistics_hourly
WHERE rrd_name='traffic_in'
ORDER BY average DESC
LIMIT 10;

I'm trying to write a Superlink Page which diplays this 10 DS/Graphs. Therefor i need the GraphID to build the correct URLs.

What would be the best join to select the corresponding GraphID.

[Howie]

I'd like to know the answer to this too. I know how to do it for a single local_data_id, but not as a join for a list....

Here's how to get a local_graph_id that contains a local_data_id:

Code: Select all

select graph_templates_item.local_graph_id as lgi FROM graph_templates_item,graph_templates_graph,data_template_rrd where graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id  and task_item_id=data_template_rrd.id and local_data_id =$local_data_id LIMIT  1;

So that's the first graph that contains a graph_item that refers to your local_data_id. It all goes a bit wrong once you start adding extra tables to the join though (for me anyway).

[henkwiedig]

Howie posted here http://forums.cacti.net/viewtopic.php?p=174235#174235 a Superlink Page which answers this question.

Code: Select all

SELECT 
DISTINCTROW dss.rrd_name, 
dss.calculated,
graph_templates_graph.local_graph_id, 
data_template_rrd.local_data_id, 
dtd.name_cache 
FROM 
graph_templates_item, 
graph_templates_graph, 
data_template_rrd, 
data_template_data dtd, 
data_source_stats_hourly_last dss 
WHERE  
graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id
and dtd.local_data_id = dss.local_data_id
and graph_templates_item.task_item_id=data_template_rrd.id
and dtd.local_data_id = data_template_rrd.local_data_id 
and dss.calculated > 0 
and (rrd_name='errors_in' or rrd_name='errors_out') 
order by name_cache, rrd_name

[Howie]

Yeah, I finally figured out where my query was going wrong

[Netcooler]

Guys - would I be wrong at this point to think that if I installed this plugin I could then connect to the MySQL DB with Crystal Reports and graph\display the data?

Am I misunderstanding that? and for Howies sake blah blah...

[phalek]

My understanding is that DSStats give you just one data item.

E.g. the last error counter of an interface

and averages for hour(?) day week ...


But it does not have all data of today, or a week.

I think that is what the "Transporter" plugin is for, which actually saves the polled data into the mysql database ( which then gets quite hudge ... )

Correct me if I am wrong here

[TheWitness]

It aggregates. So, you have X samples of actual values, Y hours of hourly averages, Z days of daily averages, etc... for every data source there is. The value of Y is configurable.

TheWitness