05-05-2023 01:45 PM - edited 05-11-2023 06:05 AM
LS has a lot of information about VMware virtual machines but too less reports.
And I create report which combine data from virtualize Linux and Windows assets.
VMware vCenter scan is not required.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
SubString(tblAssets.Description, 1, 25) As description,
tblVmwareGuest.Name As VMName,
tblAssets.userdomain,
tblAssets.username,
Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease,
tblVmwareGuest.guestfullname) As OS,
tblAssets.IPAddress,
tblVmwareGuest.NumVirtualDisks,
tblVmwareGuest.NumEthernetCards,
tblVmwareGuest.MacAddress,
tblVmwareInfo.HostName,
tblVmwareInfo.version As ESXi_Version,
tblVmwareGuest.CpuCount As VMCoreCount,
tblVmwareGuest.Memory As VMVemory,
Coalesce(tblBaseBoard.version, tblLinuxBaseBoard.version) As MB_Version,
tblBIOS.caption As BIOS_Win_Caption,
Coalesce(tblBIOS.releasedate, tblLinuxBios.releasedate) As BiosDate,
Coalesce(tblBIOS.Version, tblLinuxBios.version) As Bios_Version,
tblAssetCustom.Serialnumber,
Cast(tblVmwareGuest.UnsharedStorage As bigint) / 1024 / 1024 As VMUsedSize,
Coalesce((Select Sum(Cast(Cast(tblLinuxHardDisks.Size As BigInt) /
1024 As Numeric)) From tblLinuxHardDisks
Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
(tblLinuxHardDisks.filesystem Like '/dev/s%' Or
tblLinuxHardDisks.filesystem Like '/dev/mapper/%')), '0') +
Coalesce((Select Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 /
1024 As Numeric)) From tblDiskdrives
Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize,
Coalesce(Stuff((Select ', ' + IsNull(tblFloppy.Model, ' ') + '(' +
IsNull(Cast(Ceiling(tblFloppy.Size / 1024 / 1024 / 1024) As varchar(30)),
'') + 'GB)' From tblFloppy
Where tblAssets.AssetID = tblFloppy.AssetID And tblFloppy.Size > 0
For Xml Path('')), 1, 2, ''), ' ') + Coalesce(Stuff((Select ', ' +
IsNull(tblLinuxHardDisks.Filesystem, ' ') + '(' +
IsNull(Cast((Cast(tblLinuxHardDisks.Size As bigint) / 1024 /
1024) As varchar(30)), '') + ' GB)' From tblLinuxHardDisks
Where tblAssets.AssetID = tblLinuxHardDisks.AssetID And
(tblLinuxHardDisks.filesystem Like '/dev/s%' Or
tblLinuxHardDisks.filesystem Like '/dev/mapper/%') And
Cast(tblLinuxHardDisks.size As bigint) > 0 For Xml Path('')), 1,
2, ''), ' ') As HDDs,
Stuff((Select ', ' + IsNull(tblUSBDevices.Name, ' ') From tblUSBDevices
Where tblAssets.AssetID = tblUSBDevices.AssetID And
tblUSBDevices.Name Not Like N'%Стандарт%' And
tblUSBDevices.Name Not Like '%HID%' And tblUSBDevices.Name Not Like
'%hub%' And tblUSBDevices.Name Not Like N'%концентратор%' And
tblUSBDevices.Name Not Like N'%совместим%' And
tblUSBDevices.Name Not Like N'%устройств%' And
tblUSBDevices.Name Not Like '%generic%' And tblUSBDevices.Name Not Like
N'%мышь%' And tblUSBDevices.Name Not Like N'%клавиатура%' And
tblUSBDevices.Name Not Like '%bluetooth%' And
tblUSBDevices.Name Not Like '%headset%' And tblUSBDevices.Name Not Like
'%printing support%' And tblUSBDevices.Name Not Like N'%поддержка%' And
tblUSBDevices.Name Not Like '%mouse%' And tblUSBDevices.Name Not
Like '%keyboard%' And tblUSBDevices.Name Not Like '%USB Composite Device%'
And tblUSBDevices.Name Not Like '%USB Pointing Device%' And
tblUSBDevices.Name Not Like '%USB Input Device%' And
tblUSBDevices.Name Not Like '%USB Human Interface Device%' And
tblUSBDevices.Name Not Like '%IEEE 1284.4 compatible printer%' And
tblUSBDevices.Name Not Like '%fax%' And tblUSBDevices.Name Not Like
'%EWS%' And tblUSBDevices.Name Not Like '%DOT4%' And
tblUSBDevices.Name Not Like '%LEDM%' And tblUSBDevices.Name Not Like
N'%фильтр%' And tblUSBDevices.Name Not Like '%windows%' And
tblUSBDevices.Name Not Like N'%дисковый%' And
tblUSBDevices.Name Not Like '%storage%' And tblUSBDevices.Name <> '' And
tblUSBDevices.Name Not Like '%unknown%' And tblUSBDevices.Name Not Like
'%smart%card%' And tblUSBDevices.Name Not Like '%HP Universal Printing%'
And tblUSBDevices.Name Not Like '%wia driver%' For Xml Path('')), 1, 2,
'') USBs,
Case tblVmwareGuest.ToolsRunningStatus
When 1 Then 'Executing scripts'
When 2 Then 'Not running'
When 3 Then 'Running'
End As ToolsRunningStatus,
tblVmwareGuest.ToolsVersion,
Case tblVmwareGuest.ToolsVersionStatus
When 1 Then 'Current'
When 2 Then 'Out of date'
When 3 Then 'Not installed'
When 4 Then 'Unmanaged'
End As ToolsVersionStatus,
tblVmwareGuest.BootTime,
tblVmwareGuest.Version,
Case
When (tblVmwareGuest.Version In ('vmx-20') And
tblVmwareInfo.version Like '%ESXi 8%') Then 'Current'
When (tblVmwareGuest.Version In ('vmx-17', 'vmx-18', 'vmx-19') And
tblVmwareInfo.version Like '%ESXi 7%') Then 'Current'
When (tblVmwareGuest.Version In ('vmx-14', 'vmx-15') And
tblVmwareInfo.version Like '%ESXi 6.7%') Then 'Current'
Else 'Out of support'
End As VHW_UP,
tblAssets.Firstseen,
tblVmwareGuest.lastchanged
From tblVmwareGuest
Inner Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
Left Join tblAssets On tblAssets.AssetID = tblVmwareGuest.AssetID
Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblLinuxBios On tblAssets.AssetID = tblLinuxBios.AssetID
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblLinuxBaseBoard On tblAssets.AssetID = tblLinuxBaseBoard.AssetID
Where Not (tblVmwareGuest.Name Like 'cp-parent%') And
Not (tblVmwareGuest.MacAddress Is Null) And tblVmwareGuest.isrunning =
'poweredon'
Order By tblAssets.AssetName
Features:
*Only PoweredOn VMs is showing. You have to change filter to show all.
05-11-2023 07:23 AM
05-11-2023 06:54 AM - edited 05-11-2023 07:04 AM
Some fixes for data corruption from Linux Scanning (Disks Size, Bios Date format)
+filter for VDI in F[arm]№P[ool]№ format
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
SubString(tblAssets.Description, 1, 25) As description,
tblVmwareGuest.Name As VMName,
tblAssets.userdomain,
tblAssets.username,
Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease,
tblVmwareGuest.guestfullname) As OS,
tblAssets.IPAddress,
tblVmwareGuest.NumVirtualDisks,
tblVmwareGuest.NumEthernetCards,
tblVmwareGuest.MacAddress,
tblVmwareInfo.HostName,
tblVmwareInfo.version As ESXi_Version,
tblVmwareGuest.CpuCount As VMCoreCount,
tblVmwareGuest.Memory As VMVemory,
tblBIOS.caption As BIOS_Win_Caption,
Coalesce(Convert(datetime,tblLinuxBios.releasedate,101), tblBIOS.releasedate)
As BiosDate,
Coalesce(tblBIOS.Version, tblLinuxBios.version) As Bios_Version,
tblAssetCustom.Serialnumber,
Cast(tblVmwareGuest.UnsharedStorage As bigint) / 1024 / 1024 As VMUsedSize,
Coalesce((Select Sum(Cast(Cast(Case IsNumeric(tblLinuxHardDisks.Size)
When 1 Then tblLinuxHardDisks.Size
Else '0'
End As BigInt) / 1024 As Numeric)) From tblLinuxHardDisks
Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
(tblLinuxHardDisks.filesystem Like '/dev/s%' Or
tblLinuxHardDisks.filesystem Like '/dev/mapper/%')), '0') +
Coalesce((Select Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 /
1024 As Numeric)) From tblDiskdrives
Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize,
Coalesce(Stuff((Select ', ' + IsNull(tblFloppy.Model, ' ') + '(' +
IsNull(Cast(Ceiling(tblFloppy.Size / 1024 / 1024 / 1024) As varchar(30)),
'') + 'GB)' From tblFloppy
Where tblAssets.AssetID = tblFloppy.AssetID And tblFloppy.Size > 0
For Xml Path('')), 1, 2, ''), ' ') + Coalesce(Stuff((Select ', ' +
IsNull(tblLinuxHardDisks.Filesystem, ' ') + '(' + IsNull(Cast((Cast(Case
IsNumeric(tblLinuxHardDisks.Size)
When 1 Then tblLinuxHardDisks.Size
Else '0'
End As bigint) / 1024 / 1024) As varchar(30)), '') + ' GB)'
From tblLinuxHardDisks
Where tblAssets.AssetID = tblLinuxHardDisks.AssetID And
(tblLinuxHardDisks.filesystem Like '/dev/s%' Or
tblLinuxHardDisks.filesystem Like '/dev/mapper/%') And
Cast(Case IsNumeric(tblLinuxHardDisks.Size)
When 1 Then tblLinuxHardDisks.Size
Else '0'
End As bigint) > 0 For Xml Path('')), 1, 2, ''), ' ') As HDDs,
Stuff((Select ', ' + IsNull(tblUSBDevices.Name, ' ') From tblUSBDevices
Where tblAssets.AssetID = tblUSBDevices.AssetID And
tblUSBDevices.Name Not Like N'%Стандарт%' And
tblUSBDevices.Name Not Like '%HID%' And tblUSBDevices.Name Not Like
'%hub%' And tblUSBDevices.Name Not Like N'%концентратор%' And
tblUSBDevices.Name Not Like N'%совместим%' And
tblUSBDevices.Name Not Like N'%устройств%' And
tblUSBDevices.Name Not Like '%generic%' And tblUSBDevices.Name Not Like
N'%мышь%' And tblUSBDevices.Name Not Like N'%клавиатура%' And
tblUSBDevices.Name Not Like '%bluetooth%' And
tblUSBDevices.Name Not Like '%headset%' And tblUSBDevices.Name Not Like
'%printing support%' And tblUSBDevices.Name Not Like N'%поддержка%' And
tblUSBDevices.Name Not Like '%mouse%' And tblUSBDevices.Name Not
Like '%keyboard%' And tblUSBDevices.Name Not Like '%USB Composite Device%'
And tblUSBDevices.Name Not Like '%USB Pointing Device%' And
tblUSBDevices.Name Not Like '%USB Input Device%' And
tblUSBDevices.Name Not Like '%USB Human Interface Device%' And
tblUSBDevices.Name Not Like '%IEEE 1284.4 compatible printer%' And
tblUSBDevices.Name Not Like '%fax%' And tblUSBDevices.Name Not Like
'%EWS%' And tblUSBDevices.Name Not Like '%DOT4%' And
tblUSBDevices.Name Not Like '%LEDM%' And tblUSBDevices.Name Not Like
N'%фильтр%' And tblUSBDevices.Name Not Like '%windows%' And
tblUSBDevices.Name Not Like N'%дисковый%' And
tblUSBDevices.Name Not Like '%storage%' And tblUSBDevices.Name <> '' And
tblUSBDevices.Name Not Like '%unknown%' And tblUSBDevices.Name Not Like
'%smart%card%' And tblUSBDevices.Name Not Like '%HP Universal Printing%'
And tblUSBDevices.Name Not Like '%wia driver%' For Xml Path('')), 1, 2,
'') USBs,
Case tblVmwareGuest.ToolsRunningStatus
When 1 Then 'Executing scripts'
When 2 Then 'Not running'
When 3 Then 'Running'
End As ToolsRunningStatus,
tblVmwareGuest.ToolsVersion,
Case tblVmwareGuest.ToolsVersionStatus
When 1 Then 'Current'
When 2 Then 'Out of date'
When 3 Then 'Not installed'
When 4 Then 'Unmanaged'
End As ToolsVersionStatus,
tblVmwareGuest.BootTime,
tblVmwareGuest.Version,
Case
When (tblVmwareGuest.Version In ('vmx-20') And
tblVmwareInfo.version Like '%ESXi 8%') Then 'Current'
When (tblVmwareGuest.Version In ('vmx-17', 'vmx-18', 'vmx-19') And
tblVmwareInfo.version Like '%ESXi 7%') Then 'Current'
When (tblVmwareGuest.Version In ('vmx-14', 'vmx-15') And
tblVmwareInfo.version Like '%ESXi 6.7%') Then 'Current'
Else 'Out of support'
End As VHW_UP,
tblAssets.Firstseen,
tblVmwareGuest.lastchanged
From tblVmwareGuest
Inner Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
Left Join tblAssets On tblAssets.AssetID = tblVmwareGuest.AssetID
Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblLinuxBios On tblAssets.AssetID = tblLinuxBios.AssetID
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where Not (tblVmwareGuest.Name Like 'f[0-9]p[0-9]%' Or
tblVmwareGuest.Name Like 'cp-parent%') And Not (tblVmwareGuest.MacAddress Is
Null) And tblVmwareGuest.isrunning = 'poweredon'
05-05-2023 01:57 PM
Hello there!
Thanks so much for sharing! Good stuff!
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now