Forum Discussion

jewettdn423's avatar
jewettdn423
Level 3
6 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 !

  • ZenOfAnalytics's avatar
    ZenOfAnalytics
    6 months ago

    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

  • ZenOfAnalytics's avatar
    ZenOfAnalytics
    Level 4
    6 months ago

    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
      6 months ago

      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
        6 months ago

        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!!!