→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Alexander_Mock
Engaged Sweeper
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
5 REPLIES 5
RCorbeil
Honored Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper
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
Honored Sweeper II
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
Honored Sweeper II
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.