Quantcast

Oracle JDBC Custom resource type not populating

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Oracle JDBC Custom resource type not populating

BeeJay

Hi All,

 

I’m in the process of updating my Oracle monitoring to be able to support monitoring of multiple databases on a single server.   To support that, I have created a new custom resource type “dbDatabase”

 

I have a JDBC query which is supposed to be populating this custom resource type, but the data being collected is being collected at the node level, instead of the custom resource type.

 

The query definition is:

 

           <query name="LastBackedUp" ifType="all" resourceType="dbDatabase" instance-column="dbName">

             <statement>

               <queryString>

                  select a.name as dbName

                       , b.value1 as dbLastBackedUp

                  from   zen_tab_5min b

                       , v$database a

                  where b.name='days_without_backup'

               </queryString>

             </statement>

             <columns>

               <column name="dbName" alias="dbName" data-source-name="dbName" type="string"/>

               <column name="dbLastBackedUp" alias="dbLastBackedUp" data-source-name="dbLastBackedUp" type="gauge"/>

             </columns>

           </query>

 

Resource type definition:

 

   <resourceType name="dbDatabase" label="Database Statistics"

          resourceLabel="Database ${dbName} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

   </resourceType>

 

I have another custom resource type for table spaces which is working fine.  This new query and resource Type are in the same data collection group as the working query/resource Type.

 

From the collectd.log file you can see it is storing the data at the node level:

 

2017-04-10 10:35:51,871 INFO  [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting data for resource Node[17]/type[node]

2017-04-10 10:35:51,871 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AttributeGroup: Visiting Group AttrGroupType[name=LastBackedUp, ifType=all] for [node]@17

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AttributeGroup: AttrGroupType[name=LastBackedUp, ifType=all] for [node]@17.shouldPersist = true

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute dbName=DEVDB01

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute dbName=DEVDB01

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute dbName=DEVDB01

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute dbLastBackedUp=9999

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute dbLastBackedUp=9999

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute dbLastBackedUp=9999

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.r.j.JRobinRrdStrategy: createDefinition: filename [/opt/opennms/share/rrd/snmp/17/dbLastBackedUp.jrb] already exists returning null as definition

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.r.j.JRobinRrdStrategy: createRRD: skipping RRD file

2017-04-10 10:35:51,872 INFO  [Collectd-Thread-36-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: updating RRD file /opt/opennms/share/rrd/snmp/17/dbLastBackedUp.jrb with values '1491784552:9999'

2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: RRD update command completed.

 

 

 

What am I missing from my configuration that is causing this data to be recorded at the node level?

 

 

Regards,

John.

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Oracle JDBC Custom resource type not populating

BeeJay

*BUMP* Does anyone have any ideas why this isn't working?

                                                                                                       



John Blackburn



From: JohnD Blackburn [mailto:[hidden email]]
Sent: Monday, 10 April 2017 11:04 AM
To: General OpenNMS Discussion <[hidden email]>
Subject: [opennms-discuss] Oracle JDBC Custom resource type not populating

Hi All,

I'm in the process of updating my Oracle monitoring to be able to support monitoring of multiple databases on a single server.   To support that, I have created a new custom resource type "dbDatabase"

I have a JDBC query which is supposed to be populating this custom resource type, but the data being collected is being collected at the node level, instead of the custom resource type.

The query definition is:

           <query name="LastBackedUp" ifType="all" resourceType="dbDatabase" instance-column="dbName">
             <statement>
               <queryString>
                  select a.name as dbName
                       , b.value1 as dbLastBackedUp
                  from   zen_tab_5min b
                       , v$database a
                  where b.name='days_without_backup'
               </queryString>
             </statement>
             <columns>
               <column name="dbName" alias="dbName" data-source-name="dbName" type="string"/>
               <column name="dbLastBackedUp" alias="dbLastBackedUp" data-source-name="dbLastBackedUp" type="gauge"/>
             </columns>
           </query>

Resource type definition:

   <resourceType name="dbDatabase" label="Database Statistics"
          resourceLabel="Database ${dbName} (index:${index})">
     <persistenceSelectorStrategy
          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>
     <storageStrategy
          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>
   </resourceType>

I have another custom resource type for table spaces which is working fine.  This new query and resource Type are in the same data collection group as the working query/resource Type.

>From the collectd.log file you can see it is storing the data at the node level (not in the custom resource group I'm trying to set up):

2017-04-10 10:35:51,871 INFO  [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting data for resource Node[17]/type[node]
2017-04-10 10:35:51,871 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AttributeGroup: Visiting Group AttrGroupType[name=LastBackedUp, ifType=all] for [node]@17
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AttributeGroup: AttrGroupType[name=LastBackedUp, ifType=all] for [node]@17.shouldPersist = true
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute dbName=DEVDB01
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute dbName=DEVDB01
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute dbName=DEVDB01
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute dbLastBackedUp=9999
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute dbLastBackedUp=9999
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute dbLastBackedUp=9999
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.r.j.JRobinRrdStrategy: createDefinition: filename [/opt/opennms/share/rrd/snmp/17/dbLastBackedUp.jrb] already exists returning null as definition
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.r.j.JRobinRrdStrategy: createRRD: skipping RRD file
2017-04-10 10:35:51,872 INFO  [Collectd-Thread-36-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: updating RRD file /opt/opennms/share/rrd/snmp/17/dbLastBackedUp.jrb with values '1491784552:9999'
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: RRD update command completed.



What am I missing from my configuration that is causing this data to be recorded at the node level?


Regards,
John.




                                                                                                       



John Blackburn
 

 



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Oracle JDBC Custom resource type not populating

BeeJay
This seems to be a problem with how JDBCdata collection works.

I updated my query to the following to force the query to return more than 1 row:

          <queryString>
                  select a.name as "DBNAME"
                       , b.value1 as dbLastBackedUp
                  from   zen_tab_5min b
                       , v$database a
                  where b.name='days_without_backup'
                  union all
                  select dummy as "DBNAME", to_number('0') as dbLastBackedUp from dual
               </queryString>


And it is now collecting into the Custom Resource type rather than at the Node level.

I believe this is a bug, that a query with a definded resource type is collecting data at the node level rather than into the resource type if the query only returns a single row.
If a query has a resourceType defined, it should be collecting into that resource type regardless of the number of rows it returns.


                                                                                                       



John Blackburn

Solution Architect - Cloud Services

Enterprise Software


Level 1, 757 Ann Street

Fortitude Valley, Queensland, 4006
AUSTRALIA
Phone: +61 7 33033219

Mobile: +61 403 344 524
E-Mail:  [hidden email]
www.abb.com



-----Original Message-----
From: JohnD Blackburn [mailto:[hidden email]]
Sent: Thursday, 13 April 2017 8:45 AM
To: General OpenNMS Discussion <[hidden email]>
Subject: Re: [opennms-discuss] Oracle JDBC Custom resource type not populating


*BUMP* Does anyone have any ideas why this isn't working?

                                                                                                       



John Blackburn



From: JohnD Blackburn [mailto:[hidden email]]
Sent: Monday, 10 April 2017 11:04 AM
To: General OpenNMS Discussion <[hidden email]>
Subject: [opennms-discuss] Oracle JDBC Custom resource type not populating

Hi All,

I'm in the process of updating my Oracle monitoring to be able to support monitoring of multiple databases on a single server.   To support that, I have created a new custom resource type "dbDatabase"

I have a JDBC query which is supposed to be populating this custom resource type, but the data being collected is being collected at the node level, instead of the custom resource type.

The query definition is:

           <query name="LastBackedUp" ifType="all" resourceType="dbDatabase" instance-column="dbName">
             <statement>
               <queryString>
                  select a.name as dbName
                       , b.value1 as dbLastBackedUp
                  from   zen_tab_5min b
                       , v$database a
                  where b.name='days_without_backup'
               </queryString>
             </statement>
             <columns>
               <column name="dbName" alias="dbName" data-source-name="dbName" type="string"/>
               <column name="dbLastBackedUp" alias="dbLastBackedUp" data-source-name="dbLastBackedUp" type="gauge"/>
             </columns>
           </query>

Resource type definition:

   <resourceType name="dbDatabase" label="Database Statistics"
          resourceLabel="Database ${dbName} (index:${index})">
     <persistenceSelectorStrategy
          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>
     <storageStrategy
          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>
   </resourceType>

I have another custom resource type for table spaces which is working fine.  This new query and resource Type are in the same data collection group as the working query/resource Type.

>From the collectd.log file you can see it is storing the data at the node level (not in the custom resource group I'm trying to set up):

2017-04-10 10:35:51,871 INFO  [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting data for resource Node[17]/type[node]
2017-04-10 10:35:51,871 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AttributeGroup: Visiting Group AttrGroupType[name=LastBackedUp, ifType=all] for [node]@17
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AttributeGroup: AttrGroupType[name=LastBackedUp, ifType=all] for [node]@17.shouldPersist = true
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute dbName=DEVDB01
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute dbName=DEVDB01
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute dbName=DEVDB01
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute dbLastBackedUp=9999
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute dbLastBackedUp=9999
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute dbLastBackedUp=9999
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.r.j.JRobinRrdStrategy: createDefinition: filename [/opt/opennms/share/rrd/snmp/17/dbLastBackedUp.jrb] already exists returning null as definition
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.r.j.JRobinRrdStrategy: createRRD: skipping RRD file
2017-04-10 10:35:51,872 INFO  [Collectd-Thread-36-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: updating RRD file /opt/opennms/share/rrd/snmp/17/dbLastBackedUp.jrb with values '1491784552:9999'
2017-04-10 10:35:51,872 DEBUG [Collectd-Thread-36-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: RRD update command completed.



What am I missing from my configuration that is causing this data to be recorded at the node level?


Regards,
John.




                                                                                                       



John Blackburn
 

 



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Oracle JDBC Custom resource type not populating

David Hustace
> On Apr 17, 2017, at 7:32 PM, JohnD Blackburn <[hidden email]> wrote:
>
> I believe this is a bug, that a query with a definded resource type is collecting data at the node level rather than into the resource type if the query only returns a single row.
> If a query has a resourceType defined, it should be collecting into that resource type regardless of the number of rows it returns.

John,

Could you open an issue for this with a way to reproduce the problem?  Hopefully, with an open source DB such as PostgreSQL ;)

Thanks,
David



David Hustace
The OpenNMS Group, Inc.
+1 919 533 0160 x7734


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss

signature.asc (858 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Oracle JDBC Custom resource type not populating

BeeJay
I've created issue 9293 for this.

https://issues.opennms.org/browse/NMS-9293

I havn't succeeded in getting a PostgreSQL monitor to work at all yet... so I've put my Oracle based configuration in the issue.

I'll keep on trying to get PostgreSQL monitoring working and update the issue with a postges based config once I get that figured out.
                                                                                                       



John Blackburn

Solution Architect - Cloud Services

Enterprise Software


Level 1, 757 Ann Street

Fortitude Valley, Queensland, 4006
AUSTRALIA
Phone: +61 7 33033219

Mobile: +61 403 344 524
E-Mail:  [hidden email]
www.abb.com



-----Original Message-----
From: david [mailto:[hidden email]]
Sent: Wednesday, 19 April 2017 8:11 AM
To: DiscussionList List <[hidden email]>
Subject: Re: [opennms-discuss] Oracle JDBC Custom resource type not populating

> On Apr 17, 2017, at 7:32 PM, JohnD Blackburn <[hidden email]> wrote:
>
> I believe this is a bug, that a query with a definded resource type is collecting data at the node level rather than into the resource type if the query only returns a single row.
> If a query has a resourceType defined, it should be collecting into that resource type regardless of the number of rows it returns.

John,

Could you open an issue for this with a way to reproduce the problem?  Hopefully, with an open source DB such as PostgreSQL ;)

Thanks,
David



David Hustace
The OpenNMS Group, Inc.
+1 919 533 0160 x7734


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Loading...