Re: [ovirt-users] SQL : last time halted?

[Eli Mesika]

On Thu, Jul 6, 2017 at 6:48 PM, Juan Hernández  wrote:

> On 07/06/2017 02:07 PM, Nicolas Ecarnot wrote:
>
>> [For the record]
>>
>> Juan,
>>
>> Thanks to your hint, I eventually found it more convenient for me to use
>> a SQL query to find out which VM was unsed for months :
>>
>> SELECT
>>vm_static.vm_name,
>>vm_dynamic.status,
>>vm_dynamic.vm_ip,
>>vm_dynamic.vm_host,
>>vm_dynamic.last_start_time,
>>vm_dynamic.vm_guid,
>>vm_dynamic.last_stop_time
>> FROM
>>public.vm_dynamic,
>>public.vm_static
>> WHERE
>>vm_dynamic.vm_guid = vm_static.vm_guid AND
>>vm_dynamic.status = 0
>> ORDER BY
>>vm_dynamic.last_stop_time ASC;
>>
>> Thank you.
>>
>>
> That is nice. Just keep in mind that the database schema isn't kept
> backwards compatible. A minor change in the engine can make your query fail
> or return incorrect results.


​I tend to agree ​

​with Juan that if you are doing that periodically then using the Python
SDK will insure that your script will survive schema changes ​

>
> ___
> Users mailing list
> Users@ovirt.org
> http://lists.ovirt.org/mailman/listinfo/users
>
___
Users mailing list
Users@ovirt.org
http://lists.ovirt.org/mailman/listinfo/users

Re: [ovirt-users] SQL : last time halted?

[Juan Hernández]

On 07/06/2017 02:07 PM, Nicolas Ecarnot wrote:

[For the record]

Juan,

Thanks to your hint, I eventually found it more convenient for me to use 
a SQL query to find out which VM was unsed for months :


SELECT
   vm_static.vm_name,
   vm_dynamic.status,
   vm_dynamic.vm_ip,
   vm_dynamic.vm_host,
   vm_dynamic.last_start_time,
   vm_dynamic.vm_guid,
   vm_dynamic.last_stop_time
FROM
   public.vm_dynamic,
   public.vm_static
WHERE
   vm_dynamic.vm_guid = vm_static.vm_guid AND
   vm_dynamic.status = 0
ORDER BY
   vm_dynamic.last_stop_time ASC;

Thank you.



That is nice. Just keep in mind that the database schema isn't kept 
backwards compatible. A minor change in the engine can make your query 
fail or return incorrect results.

___
Users mailing list
Users@ovirt.org
http://lists.ovirt.org/mailman/listinfo/users

Re: [ovirt-users] SQL : last time halted?

[Nicolas Ecarnot]

[For the record]

Juan,

Thanks to your hint, I eventually found it more convenient for me to use 
a SQL query to find out which VM was unsed for months :


SELECT
  vm_static.vm_name,
  vm_dynamic.status,
  vm_dynamic.vm_ip,
  vm_dynamic.vm_host,
  vm_dynamic.last_start_time,
  vm_dynamic.vm_guid,
  vm_dynamic.last_stop_time
FROM
  public.vm_dynamic,
  public.vm_static
WHERE
  vm_dynamic.vm_guid = vm_static.vm_guid AND
  vm_dynamic.status = 0
ORDER BY
  vm_dynamic.last_stop_time ASC;

Thank you.

--
Nicolas ECARNOT

Le 30/05/2017 à 17:29, Juan Hernández a écrit :

On 05/30/2017 05:02 PM, Nicolas Ecarnot wrote:

Hello,

I'm trying to find a way to clean up the VMs list of my DCs.
I think some of my users have created VM they're not using anymore, but
it's difficult to sort them out.
In some cases, I can shutdown some of them and wait.
Is there somewhere stored in the db tables the date of the last VM
exctinction?

Thank you.



Did you consider using the API? There is a 'stop_time' attribute that
you can use. For example, to list all the VMs and sort them by stop time
you can use the following Python script:

---8<---
import ovirtsdk4 as sdk
import ovirtsdk4.types as types

# Create the connection to the server:
connection = sdk.Connection(
 url='https://engine.example.com/ovirt-engine/api',
 username='admin@internal',
 password='...',
 ca_file='/etc/pki/ovirt-engine/ca.pem'
)

# List the virtual machines:
vms_service = connection.system_service().vms_service()
vms = vms_service.list()

# Sort the them by stop time:
vms.sort(key=lambda vm: vm.stop_time)

# Print the result:
for vm in vms:
 print("%s: %s" % (vm.name, vm.stop_time))

# Close the connection to the server:
connection.close()
--->8---




--
Nicolas ECARNOT
___
Users mailing list
Users@ovirt.org
http://lists.ovirt.org/mailman/listinfo/users

Re: [ovirt-users] SQL : last time halted?

[Juan Hernández]

On 05/30/2017 05:02 PM, Nicolas Ecarnot wrote:
> Hello,
> 
> I'm trying to find a way to clean up the VMs list of my DCs.
> I think some of my users have created VM they're not using anymore, but
> it's difficult to sort them out.
> In some cases, I can shutdown some of them and wait.
> Is there somewhere stored in the db tables the date of the last VM
> exctinction?
> 
> Thank you.
> 

Did you consider using the API? There is a 'stop_time' attribute that
you can use. For example, to list all the VMs and sort them by stop time
you can use the following Python script:

---8<---
import ovirtsdk4 as sdk
import ovirtsdk4.types as types

# Create the connection to the server:
connection = sdk.Connection(
url='https://engine.example.com/ovirt-engine/api',
username='admin@internal',
password='...',
ca_file='/etc/pki/ovirt-engine/ca.pem'
)

# List the virtual machines:
vms_service = connection.system_service().vms_service()
vms = vms_service.list()

# Sort the them by stop time:
vms.sort(key=lambda vm: vm.stop_time)

# Print the result:
for vm in vms:
print("%s: %s" % (vm.name, vm.stop_time))

# Close the connection to the server:
connection.close()
--->8---

___
Users mailing list
Users@ovirt.org
http://lists.ovirt.org/mailman/listinfo/users

[ovirt-users] SQL : last time halted?

[Nicolas Ecarnot]

Hello,

I'm trying to find a way to clean up the VMs list of my DCs.
I think some of my users have created VM they're not using anymore, but 
it's difficult to sort them out.

In some cases, I can shutdown some of them and wait.
Is there somewhere stored in the db tables the date of the last VM 
exctinction?


Thank you.

--
Nicolas ECARNOT
___
Users mailing list
Users@ovirt.org
http://lists.ovirt.org/mailman/listinfo/users