1 Reply Latest reply on Feb 2, 2016 11:03 PM by Mike West

    How do I get MEM_EFFECTIVE max value using sql query to TSCO DB?

      Share This:

      We are trying to create a tabular report using SQL to query the TSCO database.   We tried to select SERIES_MODULE.GET_ROLL_VALUE (30,'AVG','SYS',sd.sysid,'MEM_EFFECTIVE') but the result is 'null'.   We are able to see Mem_Effective in TSCO console analyses, but can't seem to pull it with SQL query.

       

      I've looked into the functions called by SERIES_MODULE.GET_ROLL_VALUE such as GET_SERIES_ID AND GET_SERIES_CATALOG to see if I'm missing something, but they return a sysobjid which I plug into get_roll_value and I still get 'null' output.

       

      Any suggestions would be appreciated. 

        • 1. Re: How do I get MEM_EFFECTIVE max value using sql query to TSCO DB?
          Mike West

          Not all metrics are maintained in the roll table for each system type.  For example, MEM_EFFECTIVE is of the metrics that is not maintained in the roll table for its supported system types. 


          In order to produce an equivalent rolling average over the past 30 days, you would need to directly use the public view PV_SYS_DATA_D to compute the results you need.


          I created an example query below that shows how to compute values from the two different approaches so that you can compare the two methods:


          1.  Obtain MEM_USED for Generic system over past 30 days using roll tables, and

          2.  Obtain MEM_REAL_USED for Generic system over past 30 days using the public view.


          Regards,


          Michael D. West


          Example:


          SELECT ps.SYSID,

            ps.NAME,

            ps.SYSTYPEID,

            ROUND(ENT_CONF_MODULE.GET_NUM_VALUE('SYS', ps.SYSID, 'TOTAL_REAL_MEM'), 0) AS MEM_SIZE,

            ROUND(SERIES_MODULE.GET_ROLL_VALUE (30, 'AVG', 30, 'SYS', ps.SYSID, 'MEM_USED'), 0) AS MEM_USED,

            ROUND(

            (SELECT AVG(pd.AVGVALUE)

            FROM PV_SYS_METRIC pm

            JOIN PV_SYS_DATA_D pd USING (SYSMETRICID)

            WHERE TRUNC(pd.ts) >= TRUNC(sysdate - 30)

            AND pm.METRIC      = 'MEM_REAL_USED'

            AND pm.SYSID       = ps.SYSID

            GROUP BY pm.SYSID

            ),0) AS MEM_REAL_USED

          FROM PV_SYSTEM ps

          WHERE ps.SYSTYPEID = 0;