[SOLVED] Find Subset of Templates of a Given operating_system.product_name

Automate
#1

We have a large number of templates per provider (~800) and we’re populating a dynamic dialog with operating_system.product_name, and then refreshing the 2nd list of dynamic dialogs with only templates that match that OS.

The current method we’re using is this:

$evm.vmdb(:ManageIQ_Providers_Vmware_InfraManager_Template).all.each`

I want to improve the performance of this service catalog view and use the values from the API directly for OS type (knowing it may return an empty list in the 2nd dialog) https://code.vmware.com/apis/42/vsphere#https://vdc-repo.vmware.com/vmwb-repository/dcr-public/98d63b35-d822-47fe-a87a-ddefd469df06/8212891f-77f8-4d27-ab3b-9e2fa52e5355/doc/vim.vm.GuestOsDescriptor.GuestOsIdentifier.html

In my reading it looks like using

.find(:all, :conditions => [])

is no longer recommended.

What is the preferred method to grab a subset of templates without iterating over the entire list?

Gist: https://gist.github.com/rvalente/1765b166717d96b25d78bf8c47b3688f

Thanks,
Ron

#2

This SQL join gets me the data that I want in a single view, ideally what I would do is return only the VMs of a specific operating_system.product_name based on a users selection in a previous dropdown.

SELECT vms.guid,vms.name,operating_systems.product_name FROM vms
INNER JOIN operating_systems ON (vms.id = operating_systems.vm_or_template_id) 
WHERE template = true;`
#3

So here is what I have so far…

bundle exec rails c
$evm=MiqAeMethodService::MiqAeService.new(MiqAeEngine::MiqAeWorkspaceRuntime.new)
templates = $evm.vmdb(:ManageIQ_Providers_Vmware_InfraManager_Template)
templates.first.operating_system.product_name
=> "Red Hat Enterprise Linux 7 (64-bit)"

templates.find_by name: works

However I (obviously) cannot do templates.find_by operating_system:
Tracked the association to here, let me know if I am on the right track.

#4

Try this:

templates = TemplateInfra.joins(:operating_system).where("operating_systems.product_name = 'Red Hat Enterprise Linux 7 (64-bit)'")
#5

Thanks xian! This works in the Rails console, but not in the automate domain.

[----] I, [2018-03-13T06:10:02.457625 #21417:502ad0]  INFO -- : <AEMethod list_templates_by_os> === TEMPLATE BENCHMARK STARTED ===
[----] I, [2018-03-13T06:10:02.458131 #21417:502ad0]  INFO -- : <AEMethod list_templates_by_os> 	Searching for all Red Hat Enterprise Linux 7 (64-bit) templates
[----] E, [2018-03-13T06:10:02.460975 #21417:502ad0] ERROR -- : <AEMethod list_templates_by_os> NameError uninitialized constant TemplateInfra
[----] E, [2018-03-13T06:10:02.461548 #21417:502ad0] ERROR -- : <AEMethod list_templates_by_os> /site_automation/Integration/VMware/DynamicDialogs/list_templates_by_os:45:in `<main>'
[----] I, [2018-03-13T06:10:02.479644 #21417:38e0d20]  INFO -- : <AEMethod [/site_automation/Integration/VMware/DynamicDialogs/list_templates_by_os]> Ending
[----] I, [2018-03-13T06:10:02.480103 #21417:38e0d20]  INFO -- : Aborting instantiation because [Method exited with rc=MIQ_ABORT]
#6

I just realized that :frowning:

#7

So from what I am reading, I need to access this through the AE Service Model and not the actual database. I am sure this is purely due to my lack of understanding on how to access the model.

Surely there is a way to do this :slight_smile:

Thanks again for the help, your method is in Ruby, one step in the right direction from my SQL.

FWIW I was trying to run .joins on the $evm.vmdb(:ManageIQ_Providers_Vmware_InfraManager_Template) object but that fails with:

NoMethodError: undefined method `joins' for #<Class:0x0000000be3c370>
from /var/www/miq/vmdb/lib/miq_automation_engine/engine/miq_ae_method_service/miq_ae_service_model_base.rb:19:in `method_missing'
#8

Can you try this way (sorry just guessing myself as well):

templates = ManageIQ::Providers::InfraManager::Template::TemplateInfra.joins(:operating_system).where("operating_systems.product_name = 'Red Hat Enterprise Linux 7 (64-bit)'")
#9

@xian Thanks, throwing the same error:

NameError uninitialized constant ManageIQ

#10

Maybe those ActiveRecords cannot be reached from Automation, I’m not sure. Someone more knowledgeable should shed some light on this…

#11

Reading this: https://pemcg.gitbooks.io/mastering-automation-in-cloudforms-4-2-and-manage/content/peeping_under_the_hood/chapter.html -> it does obfuscate some of the Rails methods.

Thanks for the help @xian!

#12

Thanks @gmccullough for the help on Gitter. He suggested find_by_sql and it worked like a dream.

Here is the sample of automate code that is returning templates by user-selected operating system:

templates = $evm.vmdb(:ManageIQ_Providers_Vmware_InfraManager_Template).find_by_sql ["SELECT vms.guid,vms.name,operating_systems.product_name FROM vms INNER JOIN operating_systems ON (vms.id = operating_systems.vm_or_template_id) WHERE template = true AND operating_systems.product_name = ?", operating_system]
templates.each do |t|
  dialog_hash[t[:guid]] = "#{t.name}"
end
1 Like
#13

Just wanted to follow up. Thanks to @gmccullough using find_by_sql this worked perfectly!