Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Node Details - Hardware Virtual and host

jlong

When you look at node details one of the items is hardware and for virtual guests it shows

Hardware Virtual, hosted by ESX01

I'd like to write a report that includes this information but i can't locate these fields in report writer.

Anyone know how to do this?

Find more posts tagged with

Accepted answers

All comments

bshopp

Check out this thread, see if the tables I reference help you out. You will probably need to create an advanced SQL report.

jlong

which thread?

bshopp

My bad, sorry about that -

Detroiter

I am looking for something similar, but more binary in nature.

Every Node Details page lists a "Hardware" field that either reports "Physical" or "Virtual". What is the database query to return just THAT value? I only need a report that shows all my physicals and one that shows all my virtuals, even if that are still "host unknown".

t0ta11ed74

I've been looking for a way to do this forever. This one tiny piece of info is incredibly valuable, especially if you want to expose which ESX server the system is running on. Hard to believe there is still no real answer or response from SW here, and the thread was opened back in 2010. I would really like to find out how to get this value into ideally...a map (or at least a tooltip), but into an email alert would be awesome too.

t0ta11ed74

Partly utilizing the query from bshopp above, best I've done so far is pull the VM name and the ESX hostname. Not sure how to get this into a map or tooltip though. This would work in an email alert though if you wanted the ESX hostname. It would still be ideal to just grab the vaule of that mysterious node hardware field, which seems to have already figured out which host is running the VM.

DECLARE @nodeId AS INT

SET @nodeId = 1

SELECT Name,HostName FROM dbo.VIM_VirtualMachines AS T2

LEFT JOIN dbo.VIM_Hosts AS T1

([T1].[HostID] = [T2].[HostID])

vijay.raje

see below, might be help you

AlexSoul

after initial few tests it looks like this is working. I will need to run it longer to see if this indeed is picking up all of them.

To save others typing - copy-paste same SQL script below:

SELECT
n.Caption
,n.IP_Address
,n.MachineType
,h.HostName
,h.VMwareProductName AS 'Host Type'
,h.VMwareProductVersion AS 'Host Version'
FROM SolarWinds.dbo.VIM_VirtualMachines v
INNER JOIN Solarwinds.dbo.VIM_Hosts h on v.HostID = h.HostID
INNER JOIN Solarwinds.dbo.Nodes n ON v.NodeID = n.NodeID
--ORDER BY n.Caption

t0ta11ed74

Nice guys! The following will pull the node's ESX host into an alert email;

${SQL: SELECT h.HostName

FROM SolarWindsOrion.dbo.VIM_VirtualMachines v

INNER JOIN SolarwindsOrion.dbo.VIM_Hosts h on v.HostID = h.HostID

INNER JOIN SolarwindsOrion.dbo.Nodes n ON v.NodeID = n.NodeID WHERE n.NodeID = '${NodeID}'}

joedissmeyer

Excellent solution! Thanks for sharing!

I had to make a few small changes to get this working for my alert rules - probably due to the alert system changes with the recent versions of Orion. Here is the code I'm using:

${SQL: SELECT h.HostName FROM dbo.VIM_VirtualMachines v

INNER JOIN dbo.VIM_Hosts h on v.HostID = h.HostID

INNER JOIN dbo.Nodes n ON v.NodeID = n.NodeID WHERE n.NodeID = '${N=SwisEntity;M=NodeID}'}

Side note:

My infrastructure team wanted to not only include the VM host but they also wanted to know what VMware datastore the virtual machine is attached to. If you want that information just modify the query a bit to this:

${SQL: SELECT v.DatastoreIdentifier FROM dbo.VIM_VirtualMachines v

INNER JOIN dbo.VIM_Hosts h on v.HostID = h.HostID

INNER JOIN dbo.Nodes n ON v.NodeID = n.NodeID WHERE n.NodeID = '${N=SwisEntity;M=NodeID}'}