08-20-2024 09:23 PM
Good afternoon I am trying to create a report that will get a list of all servers that we have within our datacenter this report should also include the make, model, serial number, type and number of processors, amount of ram, and other custom fields.
I created one already that does almost 90% of the work I'm looking for, but I'm running into an issue where it is not finding the VMware ESXi servers so it can't find our vSAN nodes If someone could take a look at the following code that I put down and let me know what I'm doing wrong as to why I'm not seeing the vSAN nodes I'd be grateful
Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.SerialNumberScanned As [Serial Number],
tblAssets.Description,
tblAssetCustom.Comments,
tblAssets.Processor As [Processor Type],
tblAssets.NrProcessors As [# of Processors],
Cast(Round(CorrectMemory.Memory / 1024, 2, 1) As decimal(10,1)) As
[Memory (GB)],
Cast(Round(tblPhysicalMemoryArray.MaxCapacity / 1024 / 1024, 2,
1) As decimal(10,1)) As [Max Memory Capacity],
Cast(CorrectMemory.Used As numeric) As [Slots used],
tblPhysicalMemoryArray.MemoryDevices As [Slots available],
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Custom1 As Row#,
tblAssetCustom.Custom2 As Rack#,
tblAssetCustom.Custom3 As [Rack Position],
tblAssetCustom.Custom4 As [Server Size],
tblAssetCustom.Custom5 As [PP Warranty Start],
tblAssetCustom.Custom6 As [PP Warranty End],
tblAssetCustom.Custom7 As [PP Warranty #],
tblAssetCustom.Custom8 As [Verified in Zabbix],
tblAssetCustom.Custom9 As [Rubrik Backup Verified],
tblAssetCustom.Custom10 As PO#,
tblAssetCustom.Custom11 As FOP#,
tblAssetCustom.Custom12 As [FOP Approver]
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblPhysicalMemoryArray On
tblAssets.AssetID = tblPhysicalMemoryArray.AssetID
Inner Join (Select tblAssets.AssetID,
Sum(Ceiling(tblPhysicalMemory.Capacity / 1024 / 1024)) As Memory,
Count(tblPhysicalMemory.Win32_PhysicalMemoryid) As Used
From tblAssets
Left Outer Join (TsysMemorytypes
Right Outer Join tblPhysicalMemory On TsysMemorytypes.Memorytype =
tblPhysicalMemory.MemoryType) On tblAssets.AssetID =
tblPhysicalMemory.AssetID
Group By tblAssets.AssetID,
tblPhysicalMemory.MemoryType
Having tblPhysicalMemory.MemoryType <> 11) CorrectMemory On
CorrectMemory.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
[Serial Number]
08-24-2024 06:22 AM
You have to modify last "where" to remove filter for vmware and Microsoft "hardware"
08-23-2024 08:05 AM
I have created such report for hardware server inventory.
It supports Windows, Linux and ESXi:
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease,
tblVmwareInfo.Version) As OS,
tblAssets.IPAddress,
tblAssets.Mac,
tblAssets.Processor As CPUModel,
Coalesce(tblVmwareInfo.CpuMhz, tblProcessor.maxclockspeed) As CPUSpeed,
tblAssets.NrProcessors As CPU,
Cast(tblAssets.Memory As BigInt) As RAM,
Coalesce((Select Sum(Cast(Cast(tblLinuxHardDisks.Size As BigInt) /
1024 As Numeric)) From tblLinuxHardDisks
Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
tblLinuxHardDisks.filesystem Like '/dev/%'), '0') + Coalesce((Select
Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 As Numeric))
From tblDiskdrives
Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize,
tblAssetCustom.Manufacturer As Manufacturer,
tblAssetCustom.Model As Model,
Coalesce(tblBaseBoard.product, tblLinuxBaseBoard.productname) As baseboard,
Coalesce(tblBaseBoard.Serialnumber, tblLinuxBaseBoard.Serialnumber) As
baseboard_SN,
tblAssetCustom.Serialnumber,
Coalesce(tblVmwareInfo.Biosdate, tblBIOS.releasedate,
tblLinuxBios.releasedate) As BiosDate
From tblAssets
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
Left Join tblLinuxBaseBoard On tblAssets.AssetID = tblLinuxBaseBoard.AssetID
Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Left Join tblLinuxProcessors On tblAssets.AssetID = tblLinuxProcessors.AssetID
Left Join tblLinuxBios On tblAssets.AssetID = tblLinuxBios.AssetID
Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblAssets.Processor Like '%xeon%' Or tblAssets.Processor Like '%epyc%')
And tblAssetCustom.Manufacturer Not Like '%vmware%' And
tblAssetCustom.Manufacturer Not Like '%microsoft%' And
IsNull(tblAssetCustom.Manufacturer, '') <> ''
Order By tblAssets.assetName
08-23-2024 06:29 PM
This is an amazing code i thank you so much KUDOS. One thing i noticed is that it is not grabbing the virutal machines from VMWare it is grabbing the ESXi Servers but not the VM's. Do you know or have one that does all of the above but includes those VM's?
Thank you so much for your reply
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now