#
#catalog_report.rb
#
# Description:
#
begin
@debug = true
#$evm.root.attributes.sort.each { |k, v| $evm.log(:info, "root: #{k}: #{v}") } if @debug
#$evm.object.attributes.sort.each { |k, v| $evm.log("info", "object: #{k}: #{v}") } if @debug
require 'pg'
start_time = (Time.now-1.month).beginning_of_month.strftime("%Y-%m-%d")
$evm.log(:info, "Start time: #{start_time}") if @debug
end_time = (Time.now-1.month).end_of_month.strftime("%Y-%m-%d")
$evm.log(:info, "End time: #{end_time}") if @debug
year = (Time.now-1.month).year
$evm.log(:info, "Year: #{year}") if @debug
month = (Time.now-1.month).strftime("%B")
$evm.log(:info, "Month: #{month}") if @debug
hostname = $evm.object['hostname'] rescue nil
$evm.log(:info, "Hostname: #{hostname}") if @debug
username = $evm.object['username'] rescue nil
$evm.log(:info, "Username: #{username}") if @debug
password = $evm.object.decrypt('password') rescue nil
conn = PG.connect( :host => "#{hostname}", :dbname => 'vmdb_production', :port => 5432, :user => "#{username}", :password => "#{password}")
report = conn.exec("select service_templates.name, count(miq_requests.source_id)
from service_templates
left outer join miq_requests
on service_templates.id = miq_requests.source_id
where (miq_requests.source_id is not null or miq_requests.source_id is null)
and (miq_requests.created_on between to_date('#{start_time}','YYYY-MM-DD') and to_date('#{end_time}','YYYY-MM-DD') or miq_requests.created_on is null)
and service_templates.name not in ('Redshift Poweroff','Redshift Poweron','Cluster Deploy','Create Snapshot - Redshift','Change Password','Restart Cluster','Test DataSource','Multiselect','Service Update' ) group by service_templates.name, miq_requests.source_id
order by service_templates.name ASC;").values
conn.close
$evm.log(:info, "Report: #{report}") if @debug
custom_rows = ""
report.each_slice(1) do |row|
custom_rows << "| #{row[0][0]} | #{row[0][1]} |
"
end
# Get to_email_address
# to = 'tkopuru@tva.gov'
to = 'CloudAdmins@tva.gov'
# Get from_email_address
from = nil
from ||= 'AutomationAdmins@tva.gov'
# email subject
subject = "Cloudforms Usage Report"
# Build email body
body = "
Catalog Request Report (#{month}, #{year})
| Catalog Name |
Count |
#{custom_rows}
"
# Send email
$evm.log("info", "Sending email to <#{to}> from <#{from}> subject: <#{subject}>")
$evm.execute('send_email', to, from, subject, body)
rescue => err
$evm.log(:error, "[(#{err.class})#{err}]\n#{err.backtrace.join("\n")}")
exit MIQ_ABORT
end