04-23-2024 02:06 PM - edited 04-23-2024 02:13 PM
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 !
Solved! Go to Solution.
04-23-2024 02:40 PM
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
04-23-2024 02:40 PM
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
04-24-2024 05:43 AM
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
04-29-2024 07:40 AM
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!!!