Reports grouped by (user added) Host attribute?

[jewettdn423]

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 · Accepted solution]

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]

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]

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