Ram update

[Alexander_Mock]

Hey all,

I am new to lansweeper and its community. This is my first post too! Anyways, I am attempting to build a report to show all the computers on our networks. This need to include how much ram there is, what the part number is and what type of ram it is(ddr/ddr2/ddr3 ect) I would also like it to show the highest frequency it can run at (pc3/pc4 ect)

Right now I am showing Asset name, Username, Domain, IP, memory amount, speed, capacity, partnumber, manufacturer, device locator, memoryname.

What is missing: I cant seem to get it to show the type of ram and what frequency it is.

I used TsysMemorytypes.MemoryName to show if it is ddr ddr2 or ddr3 ect but It adds extra columns and doesnt show the correct type. I have yet to find out how to show pc3/4.

is there a way to sort the actual criteria when searching for ddr/2/3/4?


Here is my code so far:

Select Distinct Top 1000000 tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Memory,
tblPhysicalMemory.Speed,
tblPhysicalMemory.Capacity,
tblPhysicalMemory.PartNumber,
tblPhysicalMemory.Manufacturer,
tblPhysicalMemory.DeviceLocator,
TsysMemorytypes.MemoryName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID,
TsysMemorytypes
Where (TsysMemorytypes.MemoryName = 'ddr4' Or TsysMemorytypes.MemoryName =
'ddr3' Or TsysMemorytypes.MemoryName = 'ddr2' Or
TsysMemorytypes.MemoryName = 'ddr')
Group By tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Memory,
tblPhysicalMemory.Speed,
tblPhysicalMemory.Capacity,
tblPhysicalMemory.PartNumber,
tblPhysicalMemory.Manufacturer,
tblPhysicalMemory.DeviceLocator,
TsysMemorytypes.MemoryName

[RCorbeil]

I was wondering why you grouped your results since you aren't doing anything across them (Count, Sum, Min, Max, etc.). Since you don't appear to need it, you should be able to drop that.

Regarding your DDR4 issue, what I offer below is in no way guaranteed to be "the way" to derive it. I only offer it in illustration. If you can figure a way to identify your DDR4 RAM from its other attributes, just adjust the conditions on the first WHEN in the two CASEs.

Select Top 1000000
  tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Memory,
  tblPhysicalMemory.Speed,
  Round(tblPhysicalMemory.Speed*8, -2) AS [PC-rating],
  tblPhysicalMemory.Capacity,
  tblPhysicalMemory.PartNumber,
  tblPhysicalMemory.Manufacturer,
  tblPhysicalMemory.DeviceLocator,
  TsysMemorytypes.MemoryName,
  CASE
    WHEN TsysMemorytypes.MemoryName = 'Unknown'
           -- NOT a definitive test for DDR4
           -- just an illustration based solely on the memory speed
           -- adjust the conditions if you can reliably derive the memory type from its other characteristics
         AND tblPhysicalMemory.Speed IN (1600, 1866, 2133, 2400, 2666, 2933, 3200, 2800, 3000, 3300) THEN 'DDR4'
    WHEN TsysMemorytypes.MemoryName LIKE 'DDR%' THEN TsysMemorytypes.MemoryName
    ELSE 'unknown'
  END AS MemType
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID
  Inner Join TsysMemorytypes ON tsysmemorytypes.Memorytype = tblPhysicalMemory.MemoryType
Where
  CASE
    WHEN TsysMemorytypes.MemoryName = 'Unknown'
           -- NOT a definitive test for DDR4
           -- just an illustration based solely on the memory speed
           -- adjust the conditions if you can reliably derive the memory type from its other characteristics
         AND tblPhysicalMemory.Speed IN (1600, 1866, 2133, 2400, 2666, 2933, 3200, 2800, 3000, 3300) THEN 'DDR4'
    WHEN TsysMemorytypes.MemoryName LIKE 'DDR%' THEN TsysMemorytypes.MemoryName
    ELSE 'unknown'
  END LIKE 'DDR%'

[RCorbeil]

Well, I guessed wrong about memory type 23. Pulling a list of the known memory types rather than relying on the documentation

SELECT *
FROM TsysMemorytypes

reveals that the last few known types are

Memorytype  MemoryName
    20      DDR
    21      DDR2
    22      DDR2 FB-DIMM
    23      DDR3
    24      DDR3
    25      FBD2

No sign of DDR4.

As far as why you're getting 0 results, when I run into that I generally start by eliminating the WHERE clause to make sure I'm getting something, then I build it back up, adding individual conditions until I find the element that breaks things.

Regarding distinguishing your DDR4, since LANSweeper doesn't recognize it, I'd suggest that you'll have to do some interpretation. Start by producing a list of all the machines with "unknown" type and take a look at the other memory characteristics. You may be able to determine that, for example, if the type is unknown and the speed is IN (2666, 2800, 2400, 3000, 2133, 3200, 3300) then it's probably DDR4.

[Alexander_Mock]

Thanks for the help!

I see how to add the tsysmemorytypes.Memorytype to the query(as it was not before) and I now understand how you set the criteria in the where clause. When I switched the code I got 0 results returned.

As I was confused I went to go into the assets and look at the hardware and under "memoryname" it is labeled "Unkown" Is there a way to fix this on my machines?

[RCorbeil]

By the way, from a quick skim of my network's inventory, I wouldn't bet money on the accuracy of the memory type. Most of my machines are identified as having memory type "Unknown".

Given the database documentation lists the values of tblPhysicalMemory.MemoryType as

Value Meaning
   0  Unknown
   1  Other
   2  DRAM
   3  Synchronous DRAM
   4  Cache DRAM
   5  EDO
   6  EDRAM
   7  VRAM
   8  SRAM
   9  RAM
  10  ROM
  11  Flash
  12  EEPROM
  13  FEPROM
  14  EPROM
  15  CDRAM
  16  3DRAM
  17  SDRAM
  18  SGRAM
  19  RDRAM
  20  DDR
  22  DDR2 FB-DIMM
  24  DDR3
  25  FBD2

(I'm guessing that the FB-DIMM should be 23) it would seem that DDR4 falls in the "unknown" category. You might want to change your WHERE clause to

Where
  TsysMemorytypes.MemoryName LIKE 'DDR%' OR TsysMemorytypes.MemoryName = 'Unknown'

or

Where TsysMemorytypes.MemoryName IN ('ddr4', 'ddr3', 'ddr2', 'ddr', 'Unknown')

[RCorbeil]

As far as why you're getting so many results, it's because you're not joining TsysMemoryTypes to the rest of your query. Replace

From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID,
  TsysMemorytypes
Where (TsysMemorytypes.MemoryName = 'ddr4'
   Or TsysMemorytypes.MemoryName = 'ddr3'
   Or TsysMemorytypes.MemoryName = 'ddr2'
   Or TsysMemorytypes.MemoryName = 'ddr')

with

From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID
  Inner Join TsysMemorytypes ON tsysmemorytypes.Memorytype = tblPhysicalMemory.MemoryType
Where
  TsysMemorytypes.MemoryName LIKE 'DDR%'

(If you'd prefer to spell out the DDR types in your WHERE clause, you can do that succinctly as "TsysMemorytypes.MemoryName IN ('ddr4', 'ddr3', 'ddr2', 'ddr')".)

As far as PC3/PC4, some quick Googling reveals that PC3 = DDR3 and PC4 = DDR4.

According to this site, the official PC3/PC4 standards are:

PC4-21300 (2666MHz)
PC4-22400 (2800MHz)
PC4-19200 (2400MHz)
PC4-24000 (3000MHz)
PC4-17000 (2133MHz)
PC4-25600 (3200MHz)
PC4-26400 (3300MHz)

PC3-6400 (800MHz)
PC3-8500 (1066MHz)
PC3-10600 (1333MHz)
PC3-12800 (1600MHz)

Some quick math reveals that the PC-rating is 8x the RAM speed, rounded to the nearest hundred. Since you already know the speed, for the PC-rating, then, you'll want to add

Round(tblPhysicalMemory.Speed*8, -2)

to your query.