DIMM and RAM Hardware Report

[kjohnson]

I would like help creating two hardware reports as follows:

Report 1: It needs to shows HD model, RAM (current installed memory), DIMM (memory slots available, memory slots used, memory slots fee, maximum capacity, memory type name) along with asset name, username, OS name, IP address, model, processor, serial number, first seen and last seen.

Report 2: the same as report one but should be specific to just one computer model.

[kjohnson]

I made even more filtered reports of which system already have SSD installed compared to those that did not. The report is as follows:


SYSTEMS WITHOUT SSD DIMM & RAM REPORT:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblFloppy.Model As [HD Model],
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.Domain,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) As MaxCapacity,
  tblAssets.Memory,
  Cast(CorrectMemory.Used As numeric) As [Slots used],
  tblPhysicalMemoryArray.MemoryDevices As [Slots available],
  tblPhysicalMemoryArray.MemoryDevices - CorrectMemory.Used As [Slots free],
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner 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
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblPhysicalMemoryArray.[Use] = 3 And tblAssetCustom.State = 1 And
  Not (dbo.tblFloppy.Model Like N'%SSD%') And Not (dbo.tblFloppy.Model
  Like N'%solid%state%') And Not (dbo.tblFloppy.Model Like N'%OCZ%') And
  Not (dbo.tblFloppy.Model Like N'%solid%state%') And
  Not (dbo.tblFloppy.Model Like N'%SV300%') And Not (dbo.tblFloppy.Model Like
  N'%Sandisk X%') And Not (dbo.tblFloppy.Model Like N'%Samsung SSD%') And
  Not (dbo.tblFloppy.Model Like N'%Intel SSD%') And
  Not (dbo.tblFloppy.Model Like N'%solid%state%') And
  Not (dbo.tblFloppy.Model Like N'%SAMSUNG MZ%') And
  Not (dbo.tblFloppy.Model Like N'%Virtual%') And Not (dbo.tblFloppy.Model Like
  N'%LITEON LCH%') And Not (dbo.tblFloppy.Model Like N'%Dell%') And
  Not (dbo.tblFloppy.Model Like N'%RAID%') And Not (dbo.tblFloppy.Model Like
  N'%SanDisk SD%') And Not (dbo.tblFloppy.Model Like N'%SAMSUNG KU%') And
  Not (dbo.tblFloppy.Model Like N'%LITEONIT LCS%') And
  Not (dbo.tblFloppy.Model Like N'%MTFDDAK128MAY%') And
  Not (dbo.tblFloppy.Model Like N'%Intel MegaSR%') And
  Not (dbo.tblFloppy.Model Like N'%Generic%') And Not (dbo.tblFloppy.Model Like
  N'%Micron 1100%') And Not (dbo.tblFloppy.Model Like N'%KINGSTON SKC%') And
  Not (dbo.tblFloppy.Model Like N'%THNSN%') And Not (dbo.tblFloppy.Model Like
  N'%External%') And Not (dbo.tblFloppy.Model Like N'%Expansion%') And
  Not (dbo.tblFloppy.Model Like N'%USB%') And Not (dbo.tblFloppy.Model
  Like N'%LSI%') And Not (dbo.tblFloppy.Model Like N'%Microsoft Storage Space%')
  And Not (dbo.tblFloppy.Model Like N'%Kingston S10032%') And
  Not (dbo.tblFloppy.Model Like N'%asmedia ASMT%') And
  Not (dbo.tblAssets.Lastseen Is Null) And dbo.tblAssetCustom.State = 1

SYSTEMS WITH SSD DIMM & RAM REPORT:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblFloppy.Model As [HD Model],
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.Domain,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) As MaxCapacity,
  tblAssets.Memory,
  Cast(CorrectMemory.Used As numeric) As [Slots used],
  tblPhysicalMemoryArray.MemoryDevices As [Slots available],
  tblPhysicalMemoryArray.MemoryDevices - CorrectMemory.Used As [Slots free],
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner 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
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where (tblPhysicalMemoryArray.[Use] = 3 And tblAssetCustom.State = 1 And
  dbo.tblFloppy.Model Like N'%SSD%') Or (dbo.tblFloppy.Model Like N'%SV300%') Or
  (dbo.tblFloppy.Model Like N'%solid%state%' And Not (dbo.tblAssets.Lastseen Is
  Null) And dbo.tblAssetCustom.State = 1)

[kjohnson]

kjohnson wrote:
I would like help creating two hardware reports as follows:

Report 1: It needs to shows HD model, RAM (current installed memory), DIMM (memory slots available, memory slots used, memory slots fee, maximum capacity, memory type name) along with asset name, username, OS name, IP address, model, processor, serial number, first seen and last seen.

Report 2: the same as report one but should be specific to just one computer model.

I was able to generate the report that I want. In case someone else needs help with such a report, below is the query.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblFloppy.Model As [HD Model],
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.Domain,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) As MaxCapacity,
  tblAssets.Memory,
  Cast(CorrectMemory.Used As numeric) As [Slots used],
  tblPhysicalMemoryArray.MemoryDevices As [Slots available],
  tblPhysicalMemoryArray.MemoryDevices - CorrectMemory.Used As [Slots free],
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner 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
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblPhysicalMemoryArray.[Use] = 3 And tblAssetCustom.State = 1