Forum Discussion

jewettdn423's avatar
8 months ago

Reports grouped by (user added) Host attribute?


Hello,  

 

New user to IT Analytics.  I had a question, if anyone has added custom host attributes and figured out how to reference them in the SQL query commands. 

A few days ago Ernie graciously walked me though adding custom host attributes and I was able to load the values to the ITA database for an attribute ie: 'Svc_Grp'.    Using the Ad Hoc occupancy report,  as one example -  I am trying to group/sum the aggregation of all hosts matching a given value in that added attribute.    Total noob question, as I have yet to digest the ITA training as of yet.  If I can reference that added attribute I think I can tweak the stock template to pull by this attribute.   
TIA !

  • Take a look at APT_V_SERVER_ATTRIBUTE.  That view shows a row for each host (aka client) in the system, followed by a column for each attribute name.  In some cases (e.g. LOCATION => LOCATION_1)  the column for the attribute will have an "_1" appended.

    To add attributes, you will join APT_V_SERVER_ATTRIBUTE to other views.  For example, to add the ENVIRONMENT attribute to a client report based on APT_V_JOB, the code snippet would be:

    SELECT DISTINCT j.client_name,
    sa.environment
    FROM apt_v_job j,
    apt_v_server_attribute sa
    WHERE j.client_id = sa.host_id

  • Take a look at APT_V_SERVER_ATTRIBUTE.  That view shows a row for each host (aka client) in the system, followed by a column for each attribute name.  In some cases (e.g. LOCATION => LOCATION_1)  the column for the attribute will have an "_1" appended.

    To add attributes, you will join APT_V_SERVER_ATTRIBUTE to other views.  For example, to add the ENVIRONMENT attribute to a client report based on APT_V_JOB, the code snippet would be:

    SELECT DISTINCT j.client_name,
    sa.environment
    FROM apt_v_job j,
    apt_v_server_attribute sa
    WHERE j.client_id = sa.host_id

    • jewettdn423's avatar
      jewettdn423
      Level 3

      Thank you sir. 

       

      I tired this out in the SQL template designer just as a test:

      SELECT DISTINCT j.client_name,
      sa.service_group
      FROM apt_v_job j,
      apt_v_server_attribute sa
      WHERE j.client_id = sa.host_id

      and it did pull up the attribute values.  So progress...

      Now with any luck I can modify this report to group by the same custom attribute:

      --Author: rich.rose@aptare.com
      --Last Updated: 02/07/2018
      WITH 
      var AS (
      SELECT
      DECODE('${freeCombo2}','KB',1,'MB',1024,'GB',(1024*1024),'TB',(1024*1024*1024),'PB',(1024*1024*1024*1024)) div_by 
      FROM apt_v_dual
      )
      SELECT 
      DECODE('${freeCombo1}',
      'Client',client_host_name,
      'Master Server',master_host_name,
      'Media Server',media_host_name,
      'Storage Unit',storage_unit_label,
      'Policy',policy_name,
      'Policy Type',policy_type_name,
      'Job Type',job_type_name,
      'Schedule',schedule_name,
      'Schedule Type',schedule_type_name,
      'Try Count',try_count) unit,
      SUM(kilobytes/div_by) total_unexpired_data,
      SUM(CASE WHEN expiration_date BETWEEN sysdate AND sysdate+30 THEN kilobytes/div_by END) less_than_30,
      SUM(CASE WHEN expiration_date BETWEEN sysdate+30 AND sysdate+90 THEN kilobytes/div_by END) bt_30_and_90,
      SUM(CASE WHEN expiration_date BETWEEN sysdate+90 AND sysdate+365.25 THEN kilobytes/div_by END) bt_90_and_1yr,
      SUM(CASE WHEN expiration_date BETWEEN sysdate+365.25 AND sysdate+1825 THEN kilobytes/div_by END) bt_1_and_5yrs,
      SUM(CASE WHEN expiration_date > sysdate+1825 THEN kilobytes/div_by END) over_5yrs
      FROM apt_v_nbu_job_detail, var
      WHERE client_id IN (${hosts})
      AND start_date BETWEEN ${startDate} AND ${endDate}
      AND expiration_date > sysdate
      AND DECODE('${freeCombo1}',
      'Client',client_host_name,
      'Master Server',master_host_name,
      'Media Server',media_host_name,
      'Storage Unit',storage_unit_label,
      'Policy',policy_name,
      'Policy Type',policy_type_name,
      'Job Type',job_type_name,
      'Schedule',schedule_name,
      'Schedule Type',schedule_type_name,
      'Try Count',try_count) IS NOT NULL
      GROUP BY DECODE('${freeCombo1}',
      'Client',client_host_name,
      'Master Server',master_host_name,
      'Media Server',media_host_name,
      'Storage Unit',storage_unit_label,
      'Policy',policy_name,
      'Policy Type',policy_type_name,
      'Job Type',job_type_name,
      'Schedule',schedule_name,
      'Schedule Type',schedule_type_name,
      'Try Count',try_count)
      ORDER BY 7,6,5,4,3,2,1 DESC
       
      Thanks again!
      • DPeaco's avatar
        DPeaco
        Moderator

        jewettdn423 

        YOU are about 200 MILES ahead of me in knowing SQL Query!!! I can't even spell SQL Query much less understand what the language is and how to speak it. :( 

        Kudos to you!!!