Ad hoc Occupancy report customization
Hello,
Hoping someone with more SQL experience can guide me a bit. New to IT Analytics, moving from OpsCenter. We have the OVA template (ITA 11.4) spun-up in a lab environment to get better acquainted with reporting, before we roll it out into production soon.
We don't have a lot of hosts in our NBU catalog (under 400), but our management team likes to track systems by Service Group. Using the Host Attribute import feature we have populated a new host attribute "Service_Group" with the new attribute values populated from our inventory data dump.
Now I am in the process of attempting to reference this new host attribute within a few pre-built templates. The one I have been working on is an Ad Hoc Occupancy report from the Veritas library. The aim with this report, is to summarize the total unexpired data for 30/60/90 days grouped by the attribute "Service_Group". The goal is to group totals for all clients in each of the service groups. We have around 30 service group values.
Where I am currently stuck is in adding in the new host attribute to the query. I have added the service_group option to the combo box options list 'Report by:' in the template designer.
Where I am failing now is grasping how to group the data totals by this new custom host attribute.
In another post, a user was gracious enough to explain how to pull data from the service_group attribute, but I have yet to merge that logic into the template query.
The template we can see is pulling job detail data from apt_v_nbu_job_detail.
So I am not able to reference the service_group attribute, as I believe those values are in apt_v_server_attribute.
I gather I need to add logic to reference apt_v_server_attribute somewhere in the query?
Query for Context: (note the commented line in the freecombo1 decode)
--Author: rich.rose@aptare.com
--Last Updated: 02/07/2018
--Modified 4/26/2024
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,
--'Service Group',apt_v_server_attribute.service_group,
'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,
--'Service Group',attribute,
'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,
--'Service Group',attribute,
'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 DESCThanks!
