Ad Widget

Collapse

Which tables to use?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jjer
    Junior Member
    • Jul 2009
    • 9
    #1

    Which tables to use?

    Hi guys,

    I wanna ask you guys who're extracting data on cpu avaliabilty of zabbix back-end database. Where you guys take your data from? is it the history_uint tables or items(lastvalue) table. if it is the latter, how come the value is always NULL for me?

    what should i do so i can perform mysql query to get what i want (CPU avalibilty)?
    Tags: None
    • Calimero
      Senior Member
      • Nov 2006
      • 481
      #2
      CPU utilization is probaby stored as a float and not an integer. So you should find data in history instead of history_uint.

      Code:
      SELECT h.host, i.key_, FROM_UNIXTIME(hs.clock) when_ , hs.value
FROM hosts AS h
JOIN items AS i ON h.hostid = i.hostid
JOIN history AS hs ON hs.itemid = i.itemid
WHERE h.host = 'host name' AND i.key_ = 'system.cpu.util[,idle,avg1]'
AND hs.clock > UNIX_TIMESTAMP('2009-07-16 00:00:00')
ORDER BY hs.clock DESC
      This will get you all samples from july 16th on, for item system.cpu.util[,idle,avg1] for a given hostname.

      items.value_type tells you what type of information is stored and hence what table to look into.

      There's also the trends* tables for aggregated data which may be more helpful (and less resource intensive) for long term stats.

        Comment

        • jjer
          Junior Member
          • Jul 2009
          • 9
          #3
          thank you Calimeo, however i dont understand the conversion of time(hs.clock) into unix timestamp. pardon, im not really good at sql statements.

          FROM_UNIXTIME(hs.clock) when_

          as above, i dont really understand....
          enlightened me (:

            Comment

            • Calimero
              Senior Member
              • Nov 2006
              • 481
              #4
              Originally posted by jjer
              thank you Calimeo, however i dont understand the conversion of time(hs.clock) into unix timestamp. pardon, im not really good at sql statements.

              FROM_UNIXTIME(hs.clock) when_

              as above, i dont really understand....
              enlightened me (:
              history.clock is an unsigned integer unix timestamp (number of seconds since 1970-01-01 00:00:00). FROM_UNIXTIME() is a MySQL function that makes it a bit more readable by converting timestamps to ISO dates (2009-07-17 15:00:00).

              UNIX_TIMESTAMP() is the opposite: convert an ISO-formated date to a unix timestamp.

                Comment

                • jjer
                  Junior Member
                  • Jul 2009
                  • 9
                  #5
                  thank you Calimero. i tried running on the codes you given me for my query and it works(no errors) however, there is no document being generated as the query searched no results. Ive change the 'host name' to my designated host name and the problem still persist. My guess is in the 'system.cpu.util[,idle,avg1]'.

                  Is that the root of the problem why my query doesnt display any results? the rest of my query is same as your's Calimero.

                    Comment

                    • Calimero
                      Senior Member
                      • Nov 2006
                      • 481
                      #6
                      Originally posted by jjer
                      thank you Calimero. i tried running on the codes you given me for my query and it works(no errors) however, there is no document being generated as the query searched no results. Ive change the 'host name' to my designated host name and the problem still persist. My guess is in the 'system.cpu.util[,idle,avg1]'.

                      Is that the root of the problem why my query doesnt display any results? the rest of my query is same as your's Calimero.
                      system.cpu.util[,idle,avg] was just an example. You have to use the exact key that is configured.

                      Also check what data type is used (float ==> history, integer ==> history_uint).

                      And use phpmyadmin, mysql query browser or the mysql cli to test you query first.

                        Comment

                        • jjer
                          Junior Member
                          • Jul 2009
                          • 9
                          #7
                          YAY! i got it working! but then while compling( generate out the data there's this error i get:

                          *Filling report...
                          *Locale: English (United States)
                          *Time zone: Default
                          Error*filling*print...*Unknown*column*name*:*lastv alue
                          net.sf.jasperreports.engine.JRException:*Unknown*c olumn*name*:*lastvalue* ****at*net.sf.jasperreports.engine.JRResultSetData Source.getColumnIndex(JRResultSetDataSource.java:3 59)* ****at*net.sf.jasperreports.engine.JRResultSetData Source.getFieldValue(JRResultSetDataSource.java:11 6)* ****at*net.sf.jasperreports.engine.fill.JRFillData set.setOldValues(JRFillDataset.java:822)* ****at*net.sf.jasperreports.engine.fill.JRFillData set.next(JRFillDataset.java:786)* ****at*net.sf.jasperreports.engine.fill.JRBaseFill er.next(JRBaseFiller.java:1425)* ****at*net.sf.jasperreports.engine.fill.JRVertical Filler.fillReport(JRVerticalFiller.java:112)* ****at*net.sf.jasperreports.engine.fill.JRBaseFill er.fill(JRBaseFiller.java:911)* ****at*net.sf.jasperreports.engine.fill.JRBaseFill er.fill(JRBaseFiller.java:814)* ****at*net.sf.jasperreports.engine.fill.JRFiller.f illReport(JRFiller.java:63)* ****at*net.sf.jasperreports.engine.JasperFillManag er.fillReport(JasperFillManager.java:421)* ****at*net.sf.jasperreports.engine.JasperFillManag er.fillReport(JasperFillManager.java:251)* ****at*com.jaspersoft.ireport.designer.compiler.IR eportCompiler.run(IReportCompiler.java:898)* ****at*org.openide.util.RequestProcessor$Task.run( RequestProcessor.java:561)* ****at*org.openide.util.RequestProcessor$Processor .run(RequestProcessor.java:986)*
                          Print not filled. Try to use an EmptyDataSource

                            Comment

                            • Calimero
                              Senior Member
                              • Nov 2006
                              • 481
                              #8
                              What query did you use eventually ?

                                Comment

                                • jjer
                                  Junior Member
                                  • Jul 2009
                                  • 9
                                  #9
                                  so sorry calimero i got it already...that problem is just som technical problems. i used ireports for report generationg

                                    Comment

                                    Previous template Next
                                    Working...