Filter list VMs by IP address

[Pearl1594]

Description

This PR fixes: https://github.com/apache/cloudstack/issues/9486

Types of changes

• [ ] Breaking change (fix or feature that would cause existing functionality to change)
• [ ] New feature (non-breaking change which adds functionality)
• [X] Bug fix (non-breaking change which fixes an issue)
• [ ] Enhancement (improves an existing feature and functionality)
• [ ] Cleanup (Code refactoring and cleanup, that may add test cases)
• [ ] build/CI
• [ ] test (unit or integration test code)

Feature/Enhancement Scale or Bug Severity

Bug Severity

• [ ] BLOCKER
• [ ] Critical
• [ ] Major
• [X] Minor
• [ ] Trivial

Screenshots (if appropriate):

How Has This Been Tested?

List VMs without any filter:

Listing VMs with a specific IP:

Listing VMs with a part of an IP used as filter:

Listing VMs with an IP that doesn't match:

How did you try to break this feature and the system with this change?

[codecov[bot]]

Codecov Report

Attention: Patch coverage is 0% with 35 lines in your changes missing coverage. Please review.

Project coverage is 15.24%. Comparing base (6e6a276) to head (b910bfe). Report is 57 commits behind head on 4.19.

Files with missing lines	Patch %	Lines
...c/main/java/com/cloud/utils/db/GenericDaoBase.java	0.00%	22 Missing :warning:
...ain/java/com/cloud/api/query/QueryManagerImpl.java	0.00%	13 Missing :warning:

Additional details and impacted files

@@             Coverage Diff              @@
##               4.19    #9547      +/-   ##
============================================
+ Coverage     15.08%   15.24%   +0.15%     
- Complexity    11184    11572     +388     
============================================
  Files          5406     5406              
  Lines        472889   489497   +16608     
  Branches      57738    66537    +8799     
============================================
+ Hits          71352    74641    +3289     
- Misses       393593   406562   +12969     
- Partials       7944     8294     +350     

Flag	Coverage Δ
uitests	5.10% <ø> (+0.79%)	:arrow_up:
unittests	15.90% <0.00%> (+0.10%)	:arrow_up:

Flags with carried forward coverage won't be shown. Click here to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[Pearl1594]

@blueorangutan package

[blueorangutan]

@Pearl1594 a [SL] Jenkins job has been kicked to build packages. It will be bundled with KVM, XenServer and VMware SystemVM templates. I'll keep you posted as I make progress.

[blueorangutan]

Packaging result [SF]: ✔️ el8 ✔️ el9 ✔️ debian ✔️ suse15. SL-JID 10699

[DaanHoogland]

@vishesh92 @Pearl1594 is this ready or does it need more work?

[Pearl1594]

@DaanHoogland It works in the current state, but I wasn't able to address @vishesh92's comment, and am awaiting his response on it.

[vishesh92]

Every search with keyword param will become too expensive. We will be doing a join of user_vm table with user_vm_view which isn't a good idea. Let me spend some time on this next week.

[Pearl1594]

@blueorangutan package

[blueorangutan]

@Pearl1594 a [SL] Jenkins job has been kicked to build packages. It will be bundled with KVM, XenServer and VMware SystemVM templates. I'll keep you posted as I make progress.

[blueorangutan]

Packaging result [SF]: ✔️ el8 ✔️ el9 ✔️ debian ✔️ suse15. SL-JID 11240

[DaanHoogland]

@blueorangutan test keepEnv

[blueorangutan]

@DaanHoogland a [SL] Trillian-Jenkins test job (ol8 mgmt + kvm-ol8) has been kicked to run smoke tests

[blueorangutan]

[SF] Trillian test result (tid-11591) Environment: kvm-ol8 (x2), Advanced Networking with Mgmt server ol8 Total time taken: 45457 seconds Marvin logs: https://github.com/blueorangutan/acs-prs/releases/download/trillian/pr9547-t11591-kvm-ol8.zip Smoke tests completed. 133 look OK, 0 have errors, 0 did not run Only failed and skipped tests results shown below:

Test	Result	Time (s)	Test File

[Pearl1594]

@vishesh92 could you please help review the changes made to address the discussed issue in the searchBuilder. Thanks!

[vishesh92]

Changes look good and it's working as expected. Just one case required to handle to prevent issues in future.

SQL query before

SELECT
    DISTINCT(user_vm.id)
FROM
    user_vm
    INNER JOIN vm_instance ON user_vm.id = vm_instance.id
    INNER JOIN account accountSearch ON vm_instance.account_id = accountSearch.id
    INNER JOIN vm_template vmTemplate ON vm_instance.vm_template_id = vmTemplate.id
WHERE
    vm_instance.type = 'User'
    AND vm_instance.display_vm = ?
    AND (
        user_vm.display_name LIKE ?
        OR vm_instance.name LIKE ?
        OR vm_instance.state = ?
        OR vm_instance.instance_name LIKE ?
    )
    AND vm_instance.removed IS NULL
    AND (accountSearch.type != ?)
    AND (vmTemplate.type != ?)
ORDER BY
    vm_instance.id ASC
LIMIT
    0, 20

after:

SELECT
    DISTINCT(user_vm.id)
FROM
    user_vm
    INNER JOIN vm_instance ON user_vm.id = vm_instance.id
    LEFT JOIN user_ip_address ipAddressSearch ON user_vm.id = ipAddressSearch.vm_id
    INNER JOIN vm_template vmTemplate ON vm_instance.vm_template_id = vmTemplate.id
    LEFT JOIN nics nicSearch ON user_vm.id = nicSearch.instance_id
    AND nicSearch.removed IS NULL
    INNER JOIN account accountSearch ON vm_instance.account_id = accountSearch.id
WHERE
    vm_instance.type = 'User'
    AND vm_instance.display_vm = ?
    AND (
        user_vm.display_name LIKE ?
        OR vm_instance.name LIKE ?
        OR vm_instance.state = ?
        OR vm_instance.instance_name LIKE ?
        OR ipAddressSearch.public_ip_address LIKE ?
        OR nicSearch.ip4_address LIKE ?
        OR nicSearch.ip4_address LIKE ?
    )
    AND vm_instance.removed IS NULL
    AND (vmTemplate.type != ?)
    AND (accountSearch.type != ?)
ORDER BY
    vm_instance.id ASC
LIMIT
    0, 20

[vishesh92]

@blueorangutan package

[blueorangutan]

@vishesh92 a [SL] Jenkins job has been kicked to build packages. It will be bundled with KVM, XenServer and VMware SystemVM templates. I'll keep you posted as I make progress.

[blueorangutan]

Packaging result [SF]: ✔️ el8 ✔️ el9 ✔️ debian ✔️ suse15. SL-JID 11298

[Pearl1594]

@blueorangutan test

[blueorangutan]

@Pearl1594 a [SL] Trillian-Jenkins test job (ol8 mgmt + kvm-ol8) has been kicked to run smoke tests

[blueorangutan]

[SF] Trillian test result (tid-11629) Environment: kvm-ol8 (x2), Advanced Networking with Mgmt server ol8 Total time taken: 44616 seconds Marvin logs: https://github.com/blueorangutan/acs-prs/releases/download/trillian/pr9547-t11629-kvm-ol8.zip Smoke tests completed. 133 look OK, 0 have errors, 0 did not run Only failed and skipped tests results shown below:

Test	Result	Time (s)	Test File