Our client wants to have a report on the VM per month, how long it was running and how long it was stopped. How we can create it for him?

Plugins
1

To get started with plugins in general, please visit https://developer.morpheusdata.com.

A good place to start with reports in general would be this example:

To interrogate the database in terms running and stopped times, this SQL statement would be a good place to start from.
(Parameterization and additional filters will be needed, depending on the use case details. The dates can be calculated in the report logic and schedules for specific for recurring execution.)

SELECT 
    au.server_name AS 'VM Name',
    CASE 
        WHEN csvr.id IS NULL THEN 'Deleted'
        WHEN au.ref_type = 'container' THEN 'VM' 
        ELSE 'Discovered' 
    END AS ref_type,
    SUM(
        CASE 
            WHEN au.status = 'stopped' THEN 
                TIMESTAMPDIFF(MINUTE, start_date, end_date)
            ELSE 0 
        END
    ) AS 'Minutes Stopped',
    SUM(
        CASE 
            WHEN au.status = 'running' THEN 
                TIMESTAMPDIFF(MINUTE, start_date, end_date)
            ELSE 0 
        END
    ) AS minutes_running,
    au.zone_name AS 'Cloud Name',
    cs.name AS 'Group Name'
FROM 
    account_usage au 
    LEFT JOIN compute_zone cz ON au.zone_id = cz.id
    LEFT JOIN compute_site_zones csz ON cz.id = csz.zone_id
    LEFT JOIN compute_site cs ON csz.site_id = cs.id
    LEFT JOIN compute_server csvr ON au.server_id = csvr.id
WHERE   
    au.ref_type in ('container', 'discoveredServer')
GROUP BY 
    au.server_name, 
    CASE 
        WHEN csvr.id IS NULL THEN 'Deleted'
        WHEN au.ref_type = 'container' THEN 'VM' 
        ELSE 'Discovered' 
    END,
    au.zone_name,
    au.refuuid,
    cs.name,
    csvr.id;`Preformatted text`

Regards.

(Thanks Chris T for the help in resolving!!)

1 Like
2

@Neil_Van_Rensburg - Thanks for sharing the query, however in the account_usage table it shows the status of running/stopped. But they want to know the status of delete instance too.

3

It appears that the compute_server column doesn’t contain deleted VMs, which gives us a way to update the ref_type column based on this. I have updated the SQL statement above to implement this.

4

@Neil_Van_Rensburg - Thanks Neil for sharing the updated query …However its shows the null value as vm name … As I checked in the account_usage table ref_name is null.

image
image848×434 74.2 KB

image
image693×511 45.1 KB

5

Updated the above SQL to use server_name in favour of ref_name. Bear in mind that duplicates will show where the same same VM exists accross multiple clouds and groups (where the same cloud is added to Morpheus more than once).