Custom Asset Inventory Report

[C1Hol]

Hello,

i try to generate a report similar to our Asset Inventory Excel Sheet. Instead of filling out the sheet manually i want to generate a report and export it into Excel.

Our Excel Sheet has the following columns:

Location (based on IP Location)
Room Number (Custom Field 2)
Host Name (Assetname)
Hardware (physical or virtual)
Manufacturer
Model
Type (Desktop, Tower, Rackserver)
Serial
Deployed OS
Support Status (active, out of warranty)
Warranty Start
Warranty End
IP Adress
MAC Address (primary)
Hypervisor (if virtual)
VM Host
Processor
Memory
Diskpartition ( for example C:50GB/D:100GB)

This is what i got until now.

Select Top 1000000 web40repIPLocationlist.IPLocation,
  tblAssetCustom.Custom2 As [Room Number],
  tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tblAssetCustom.Manufacturer As Vendor,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As [Service Tag Number],
  tblAssetCustom.Warrantydate As [Warranty End Date],
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssets.Processor,
  tblAssets.Memory
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join web40repIPLocationlist On web40repIPLocationlist.AssetID =
    tblAssets.AssetID
Where tblAssetGroups.AssetGroup Like '%Server%'

Here are my Problems i need some help with 🙂
I tried to find the fields but i wasn't successful and i'm a report beginner

1. Is there a field for VM or physical?
2. Is there a field for Device Type (Laptop, Desktop, Server,...)
3. When i use tblAssetMacAddress.Mac every Mac Address is shown but i need just the primary which belongs to the IP Adress of tblAssets.IPAddress
4. A way to get a column for the Hypervisor and another one for the VM Host. When i open an Asset i can see a Field like VMware Servername or Hyper-V Servername but i wasn't able to use it in my Report.
5. In the table tsysOS.OSnameis just a short form of the Operatingsystem Name. Is there also a long form like "Windows 7 Enterprise x64 SP1" instead of "Win 7"
6. Is it possible to Display the partitions like C:\50GB D:\100GB

Thanks in advance

[Hemoco]

We added 1 and 6 to your original report and marked what was added.

Select Top 1000000 web40repIPLocationlist.IPLocation,
tblAssetCustom.Custom2 As [Room Number],
tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblAssetCustom.Warrantydate As [Warranty End Date],
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Processor,
tblAssets.Memory,
Case when tblAssetCustom.Model like '%virtual%' then 'virtual' else 'physical' end as VirtualCheck,
tblDiskdrives.Caption + '\' + Cast(Ceiling(tblDiskdrives.Size / 1024 / 1024 / 1024) As nvarchar) as DiskSize
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join web40repIPLocationlist On web40repIPLocationlist.AssetID =
tblAssets.AssetID
Inner Join tblDiskDrives on tblDiskDrives.AssetID = tblAssets.AssetID
Where tblAssetGroups.AssetGroup Like '%Server%'

[C1Hol]

Thank you very much for your Reply.

Like i mentioned before i'm pretty bad with SQL and creating Reports. When it's just dragging and clicking it's ok but everything else gets complicated

So could you give me a hand on 1. and 6.
Copy and paste doesn't work so good. I tried different things but it's not working.

I guess 2,3 and 4 i can implement with adding the tables and drawing lines in between

[Hemoco · Accepted solution]

1. There is no specific field for this. You could make the distinction based on tblAssetCustom.Model. A sample expression is seen below.

Case when tblAssetCustom.Model like '%vmware%' then 'virtual' else 'physical' end

2. There is a chassis field that describes a machine's physical container. Sample report below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysChassisTypes.ChassisName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Where tblAssetCustom.State = 1

3. It would be best to add tblNetwork to your report and exclude non-IP enabled network cards.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblNetwork.Description As NIC,
tblNetwork.MACaddress
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPEnabled = 1 And tblAssetCustom.State = 1

4. Sample report below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.SP,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Memory,
tblComputersystem.Lastchanged,
tsysOS.Image As icon,
tblAssets.IPAddress,
tblAssets1.AssetName As HyperVhost
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
Inner Join tblAssetMacAddress on tblassets.AssetID = tblAssetMacAddress.AssetID
Inner Join TblHyperVGuestNetwork on TblHyperVGuestNetwork.MacAddress = tblAssetMacAddress.Mac
Left Join TblHyperVGuest on TblHyperVGuestNetwork.HyperVGuestID = TblHyperVGuest.hypervguestID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblHyperVGuest.AssetID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1

5. Full OS name is stored in tblOperatingsystem.Caption, service pack in tblAssets.SP and architecture (32-bit/64-bit) in tblComputersystem.SystemType.
6. Sample expression below.

tblDiskdrives.Caption + '\' + Cast(Ceiling(tblDiskdrives.Size / 1024 / 1024 / 1024) As nvarchar)