graphs many values in one column jdbc

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

graphs many values in one column jdbc

maksuPL
Hello,
I want to graph a databases through opennms.
Here is my problem :

I have the following table in postgresql

A B Value
A1 B1 55
A1 B2 60
A1 B3 98
A2 B1 75
A2 B2 54
A2 B3 32

The table is called « test »
I want to realize two graphs : one graph for A1 and one for A2. Then in each graph there will be all values B represented. For example in the graph for A1 there will be three cruves : the first curve for B1 with the value 55, the second for B2 with value 60 and the third for B3 with 98. Off course the numerical value will change in the future so we will see the evolution of the cruves. And I want to do the same with the graph for A2.
I realized the tutorial about jdbc collection http://www.opennms.org/wiki/JDBC_Data_Collection_Tutorial
 and I made the resource type for A. So now I have two graphs for A1 and A2. But only the value B1 appeared in the graph.
I put below my configuration files :

<jdbc-collection name="postgresql-test">
  <rrd step="300">
    <rra>RRA:AVERAGE:0.5:1:2016</rra>
    <rra>RRA:AVERAGE:0.5:12:1488</rra>
    <rra>RRA:AVERAGE:0.5:288:366</rra>
    <rra>RRA:MAX:0.5:288:366</rra>
    <rra>RRA:MIN:0.5:288:366</rra>
  </rrd>
  <queries>
    <query name="AB" ifType="ignore" instance-column=”A” ressourceType = “typeA”>
      <statement>
        <queryString>
            SELECT A, B, sum(value) as v1
            FROM test
            GROUP BY A,B
        </queryString>
      </statement>
      <columns>
        <column name="v1" data-source-name="v1"
            alias="v1" type="counter"/>
        <column name="B" data-source-name="B"
            alias="B" type="string"/>

      </columns>
    </query>
  </queries>
</jdbc-collection>

jdbc-datacollection-config.xml

Below I put the file for configuration the resource type. It is in the datacollection folder and it is called postgresql-test.xml
<datacollection-group name="Postgres test">
    <resourceType name="typeA" label="Postgres test"
           resourceLabel="(index:${index})">
      <persistenceSelectorStrategy
           class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/>
      <storageStrategy
           class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/>
    </resourceType>
</datacollection-group>


And now I put the file for draw the graph. I put its in the folder snmp-graphs.properties.d as it is write in the tutorial

reports=postgres-test.test

report.postgres-test.test.name=Postgres test
report.postgres-test.test.columns=v1
report.postgres-test.test.propertiesValues=A,B
report.postgres-test.test.type=typeA
report.postgres-test.test.command=--title="A {A}" \
 --vertical-label="B" \
 DEF:b={rrd1}:B:AVERAGE \
 AREA:b#0000cc:"{B}" \
 GPRINT:b:AVERAGE:"Avg: %8.2lf %s" \
 GPRINT:b:MIN:"Min: %8.2lf %s" \
 GPRINT:b:MAX:"Max: %8.2lf %s\\n"

postgresql-test.properties
Ofcourse all the file as collectd etc work very well.
So how to show the curve B1,B2,B3 in the same graph (for A1 for exmple) ?
Thank you for help