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

Report that lists all windows servers physical and VM with OS version and patch level

any help with this would be greatly appreciated.

I am trying to create a report that includes all our VM servers and Physical servers that reports System Name, IP address and OS and version or patch level.

I have this

SELECT TOP 10000 Nodes.Caption AS NodeName,
Nodes.IP_Address AS ESX_Host_IP_Address,
VirtualMachines.DisplayName AS VM_Host,
VirtualMachines.IPAddress AS VM_Host_IP_Address,
VirtualMachines.GuestState AS GuestState,
VirtualMachines.OperatingSystem AS OperatingSystem,
Nodes.NodeID AS NodeID,
VirtualMachines.NodeID AS NodeID_1

FROM
VirtualMachines INNER JOIN Nodes ON (VirtualMachines.HostNodeID = Nodes.NodeID)

But how do I add the physcal servers to this so that report catches all our servers?

Thanks in Advance for any help

Find more posts tagged with

Accepted answers

All comments

bobross

I would use a nested Select or a stored transaction to gather the data from the physical servers.

profzoom1

Thanks bobross my issue is I do not know how to do that, can you supply that for this custom SQL report and upload it to the content exchange?

I would upload it if you can't and can get me the rest of what I need to make this work. I am very limited as to what I can do in SQL to get these to work.

Thnaks in advance

profzoom1

I have opened a ticket with Solarwinds support - Case Update: 332299

You would think this would be a no brainer to someone who can create the SQL query - that is not me.

If someone could do that it would sure help out more people than just me, we are trying to get this report so that we can true up or MS licenses. It would also help if the report could identify SQL servers as well.

profzoom1

Support will not help in customizing the report for the SQL part but they are trying to assist me with just the report writer but nothing yet.

Really need the community to help on this one since I am not a SQL script writer.

netlogix

The SQL I can help with, but I don't know where you are going to get the OS version and patch level, but here is something:

SELECT Nodes.Caption AS NodeName,

Nodes.IP_Address AS Host_IP_Address,

VirtualMachines.DisplayName AS VM_Host,

VirtualMachines.IPAddress AS Guest_IP_Address,

VirtualMachines.GuestState AS GuestToolsState,

VirtualMachines.OperatingSystem AS OperatingSystem,

Nodes.NodeID AS NodeID,

VirtualMachines.NodeID AS NodeID_1

FROM

VirtualMachines INNER JOIN Nodes ON (VirtualMachines.HostNodeID = Nodes.NodeID)

Union

Select Nodes.Caption AS NodeName,

Nodes.IP_Address AS Host_IP_Address,

'N/A' AS VM_Host,

'N/A' AS Guest_IP_Address,

'N/A' AS GuestToolsState,

Nodes.MachineType AS OperatingSystem,

Nodes.NodeID AS NodeID,

0 AS NodeID_1

from nodes

where not Nodes.NodeID in (select NodeID from VirtualMachines)

and vendor = 'Microsoft'

You might take out the "and vendor = 'Microsoft", but that returned alot of cisco stuff for me.

profzoom1

Thanks netlogix but that still only pulled in my virtual servers and did not report on any of my physical boxes.

I have a report I am trying to do with the report writer and custom properties that Oliver a Tech at Solarwinds pointed me to doing. the SQL part looks like this so far.

SELECT
Nodes.SysName AS System_Name, Nodes.IP_Address AS IP_Address, Nodes.Role_Type AS Role_Type, Nodes.MachineType AS Machine_Type, Nodes.Vendor AS Vendor
FROM
Nodes
WHERE
(
(Nodes.Model = 'Server')
)

ORDER BY 3 ASC