How can I report on virtual machine runtime for the month?

We are running SolarWinds Observability Self-Hosted Advanced version 2024.2.1. We host virtual machines for customers in our environment on VMware and Hyper-V hypervisors. We charged based on the amount of time the virtual machine is running, then bill the customer at the end of the month. We have a manual process to check these now, but it would be a lot nicer if we could just run a report in SolarWinds that reports the runtime for any virtual machine.

I have been testing with our VMware environment and I'm capturing logs in SolarWinds Log Viewer. I have also created a rule that looks for specific events and triggers an informational alert on these. Now I just need to figure out how to capture these events/alerts in a report, that includes the date, timestamp, and VM name for the month.

The information I'm capturing is the following:

In Log Viewer, a power-on message looks like this, including the tag I created for the rule:

9/10/2025
8:34:31 AM
vcenter
VM PowerOn PowerOff
OpenVAS Vulnerability Scanner - MB 20250620 on 192.168.1.10 in 65_Datacenter is starting
The details of this event is the following:
ENTRY DETAILS
9/10/2025 8:34:31 AM
OpenVAS Vulnerability Scanner - MB 20250620 on 192.168.1.10 in 65_Datacenter is starting
Source Time
9/10/2025 8:34:30 AM
S
ource
vcenter
(192.168.1.10)
Vendor
VMware Inc.
Machine Type
VMware vCenter Server
Log Type
VMware Events
Level
Information
Tags
VM PowerOn PowerOff
EventType
VmStartingEvent
Key
89279006
ChainId
89279005
I'm also getting proper Informational alerts for this with the following:
Alert Name: VM PowerOn PowerOff
Message: VM PowerOn PowerOff was triggered
The details for this alert contains all of the information, such as the event it was triggered on: OpenVAS Vulnerability Scanner - MB 20250620 on 192.168.1.10 in 65_Datacenter is starting, as well as the time and date.
I can also create two alerts if that works better, one for VM PowerOn and a separate alert for VM PowerOff, if that makes it easier to pull the events from.

Find more posts tagged with

Accepted answers

All comments

Magnus_Knight

I so nicely laid out all the formatting to make it easier to read, and when I posted it all of the lines are crammed together. Sorry for that.

LeBeauUK

Have you looked at the Chargeback under the Virtualization menu as an alternative?

Magnus_Knight

I have not, but I will check this. Thank you!

Magnus_Knight

Thank you for the suggestion, but it doesn't appear Chargeback will work for our situation. The way our billing department does this is they bill strictly on runtime of the VM in 1-hour increments. For example, let's say we charge $1 per hour for VM runtime, if a VM runs for 1 minute up to 60 minutes, they are charged $1 for that VM, if it runs anywhere between 61 minutes to 120 minutes, they are charged $2 for that VM, and so on. I can't find a way to use Chargeback to work with this model.

LeBeauUK

Try this SWQL

SELECT 
  n.NodeID,
  n.Caption AS [Server Name],
  n.IP_Address,
  n.MachineType,
  DATEDIFF(MINUTE, n.LastBoot, GETDATE()) / (24*60) AS [Uptime_Days],
  (DATEDIFF(MINUTE, n.LastBoot, GETDATE()) % (24*60)) / 60 AS [Uptime_Hours],
  DATEDIFF(MINUTE, n.LastBoot, GETDATE()) % 60 AS [Uptime_Minutes],
  CONCAT(
    DATEDIFF(MINUTE, n.LastBoot, GETDATE()) / (24*60), ' days, ',
    (DATEDIFF(MINUTE, n.LastBoot, GETDATE()) % (24*60)) / 60, ' hours, ',
    DATEDIFF(MINUTE, n.LastBoot, GETDATE()) % 60, ' minutes'
  ) AS [Formatted Uptime]
FROM 
  Orion.Nodes n
WHERE 
  n.MachineType LIKE '%Server%' AND
  n.MachineType NOT LIKE '%VMware%' -- exclude ESX hosts if needed
ORDER BY 
  n.LastBoot ASC

You could also change the first where statement to be Caption, if the servers are named in SolarWinds are certain way.

Magnus_Knight

Thank you, LeBeauUK. I really appreciate you putting this together.

When I ran the one you provided here in SolarWinds SWQL Studio, I received the error "Cannot resolve property MINUTE". I have been researching this and trying similar queries myself and I have been running into similar errors with my own attempts as well. Many of the objects and properties I try to test with seem to fail, but documentation and other people's working queries seem to indicate that they should work, so I don't know what's wrong with my SolarWinds setup. I have also had it fail to resolve HOUR, and many others. I think this is the right track, but I think there is something wrong with my SolarWinds or something.

I found a query that works, but does not use Log Viewer logs, but the problem with this is it seems to rely on polling intervals to see if the VMs are running, and the problem with that is if a VM runs for a short time where the polling interval misses it, then it's not reported. I tested a VM where I booted it three times for a total of 64 minutes runtime, and using this method SolarWinds reported a 4.11% availability, which is roughly 59 minutes, so it's close, but still a bit off. The Log Viewer returns exact times, to the fraction of a second, so I hope I can find a way to use that.

This is the query I mentioned:

SELECT 
    [data].[Name] AS [Virtual Machine], 
    [data].[Host].[HostName] AS [Hypervisor Host], 
    [data].[MemoryConfigured] / 1073741824 AS [RAM GB], 
    RP.Name AS [Resource Pool], 
    SUM(1.0 * [data].[VMStatistics].[Availability] * [data].[VMStatistics].[Weight]) / 
        SUM(1.0 * CASE WHEN [data].[VMStatistics].[Availability] IS NULL THEN 0 ELSE [data].[VMStatistics].[Weight] END) AS [Availability %],
    CEILING(
        (SUM(1.0 * [data].[VMStatistics].[Availability] * [data].[VMStatistics].[Weight]) / 
         SUM(1.0 * CASE WHEN [data].[VMStatistics].[Availability] IS NULL THEN 0 ELSE [data].[VMStatistics].[Weight] END)) / 
        100 * (DAY(ADDDAY(-1, DateTrunc('month', GETDATE()))) * 24)
    ) AS [Running Hours (Rounded Up)],
    CEILING(
        (SUM(1.0 * [data].[VMStatistics].[Availability] * [data].[VMStatistics].[Weight]) / 
         SUM(1.0 * CASE WHEN [data].[VMStatistics].[Availability] IS NULL THEN 0 ELSE [data].[VMStatistics].[Weight] END)) / 
        100 * (DAY(ADDDAY(-1, DateTrunc('month', GETDATE()))) * 24)
    ) * ([data].[MemoryConfigured] / 1073741824) AS [Billing Amount]
FROM Orion.VIM.VirtualMachines AS data
LEFT JOIN Orion.VIM.ResourcePools RP ON [data].[ResourcePoolID] = RP.ResourcePoolID
WHERE 
    [data].[VMStatistics].[ObservationTimestamp] >= DateTrunc('month', ADDMONTH(-1, GETDATE()))
    AND [data].[VMStatistics].[ObservationTimestamp] < DateTrunc('month', GETDATE())
    AND (
        ([data].[PlatformID] = 1 AND RP.Name != 'Root' AND RP.Name IS NOT NULL) -- VMware, exclude Root
        OR ([data].[PlatformID] = 2 AND ([data].[Name] LIKE 'cust1%' OR [data].[Name] LIKE 'cust2%' OR [data].[Name] LIKE 'cust3%')) -- Hyper-V, customer prefixes
    )
    AND [data].[Name] NOT LIKE 'vse%'
GROUP BY 
    [data].[Name], 
    [data].[Host].[HostName], 
    [data].[MemoryConfigured], 
    RP.Name
HAVING 
    SUM(1.0 * [data].[VMStatistics].[Availability] * [data].[VMStatistics].[Weight]) / 
        SUM(1.0 * CASE WHEN [data].[VMStatistics].[Availability] IS NULL THEN 0 ELSE [data].[VMStatistics].[Weight] END) > 0
ORDER BY 
    [Virtual Machine] ASC