Lansweeper reports: general hardware info, disks, memory, monitors
Contents
Notes
These are Lansweeper reports with some basic information about computers’ hardware. You can find more reports created by the community on the forum.
- Lansweeper datbase documentation can be accessed from the web console: Reports 🡢 Database Documentation;
- columns with hyperlink_ and hyperlink_name_ prefixes in their names are used to place hyperlinks into cells of a Lansweeper report as described in this post;
- reports were created for the Lansweeper installation that uses MS SQL Server and may contain T-SQL-specific approaches to some operations (for example, string concatenation);
- reports were created in Lansweeper v. 9.0.0.17.
Report 1: general hardware info
Report contains general information about computer hardware:
- date of the last successful scan of a computer;
- computer name;
- last logged on user name;
- IP address (the last IP to be scanned);
- MAC address;
- number of monitors connected to the computer;
- CPU model;
- RAM capacity;
- number and capacities of disks;
- system (or mainboard) model.
T-SQL query:
SELECT
CAST([tblAssets].[LastSeen] AS date) AS [Scan Date]
,'https://ls.adatum.local/asset.aspx?AssetID='
+ cast([tblAssets].[AssetID] AS nvarchar(100)) AS [hyperlink_Computer Name]
,[tblAssets].[AssetName] AS [hyperlink_name_Computer Name]
,'https://ls.adatum.local/user.aspx?username=' + [tblAssets].[Username]
+ '&userdomain=' + [tblAssets].[Userdomain] AS [hyperlink_Last Logged on User]
,[tblAssets].[Userdomain] + N'\'
+ [tblAssets].[Username] AS [hyperlink_name_Last Logged on User]
,[tblAssets].[IPAddress] AS [IP Address]
,[tblAssets].[Mac] AS [MAC Address]
,(
SELECT COUNT(*)
FROM [lansweeperdb].[dbo].[tblMonitor] AS [Inner]
WHERE [tblAssets].[AssetID] = [Inner].[AssetID]
) AS [Monitors Count]
,'https://www.google.com/search?q='
+ [tblAssets].[Processor] AS [hyperlink_Processor (Click to Google)]
,[tblAssets].[Processor] AS [hyperlink_name_Processor (Click to Google)]
,CAST([tblAssets].[Memory] / 1024 AS int) AS [Memory (GB)]
,(
SELECT COUNT(*)
FROM [lansweeperdb].[dbo].[tblFloppy] AS [Inner]
WHERE [tblAssets].[AssetID] = [Inner].[AssetID]
) AS [Disks Count]
,STUFF(
(
SELECT N', ' + CAST(CAST(ROUND([Inner].[Size] / 1024 / 1024 / 1024, 1)
AS real)
AS nvarchar(10))
+ N' GB'
FROM [lansweeperdb].[dbo].[tblFloppy] AS [Inner]
WHERE [tblAssets].[AssetID] = [Inner].[AssetID]
ORDER BY [Inner].[Name]
FOR XML PATH(N''), TYPE
).value(N'.', N'nvarchar(1024)')
,1, 2, N''
) AS [Disks Size]
,[tblAssetCustom].[Manufacturer] AS [System Manufacturer]
,'https://www.google.com/search?q='
+ [tblAssetCustom].[Model] AS [hyperlink_System Model (Click to Google)]
,[tblAssetCustom].[Model] AS [hyperlink_name_System Model (Click to Google)]
FROM
[lansweeperdb].[dbo].[tblAssets]
INNER JOIN [lansweeperdb].[dbo].[tblAssetCustom] ON [tblAssets].[AssetID] = [tblAssetCustom].[AssetID]
WHERE
[tblAssets].[AssetType] = -1 -- -1 = "Windows"
AND [tblAssetCustom].[State] = 1 -- 1 = "Active"
ORDER BY
[tblAssets].[AssetName]
Report 2: disks
Report contains information about disks and computers in which the disks are installed:
- date of the last successful scan of a computer;
- computer name;
- last logged on user name;
- number of disks in the computer;
- disk capacity;
- number of partitions on the disk;
- disk status ("OK", "Degraded", "Pred Fail" and others — see this article);
- disk model and serial number.
T-SQL query:
SELECT
CAST([tblAssets].[LastSeen] AS date) AS [Last Successful Scan Date]
,'https://ls.adatum.local/asset.aspx?AssetID='
+ cast([tblAssets].[AssetID] AS nvarchar(100)) AS [hyperlink_Computer Name]
,[tblAssets].[AssetName] AS [hyperlink_name_Computer Name]
,'https://ls.adatum.local/user.aspx?username=' + [tblAssets].[Username]
+ '&userdomain=' + [tblAssets].[Userdomain] AS [hyperlink_Last Logged On User]
,[tblAssets].[Userdomain]
+ N'\' + [tblAssets].[Username] AS [hyperlink_name_Last Logged On User]
,(
SELECT COUNT(*)
FROM [lansweeperdb].[dbo].[tblFloppy] AS [Inner]
WHERE [tblAssets].[AssetID] = [Inner].[AssetID]
) AS [Num of Disks in Computer]
,CAST(ROUND([tblFloppy].[Size] / 1024 / 1024 / 1024, 1) AS real) AS [Disk Size (GB)]
,[tblFloppy].[Partitions] AS [Partitions Count]
,RIGHT([tblFloppy].[Name], 1) AS [Disk Number]
,[tblFloppy].[Status] AS [Disk Status]
,'https://www.google.com/search?q='
+ [tblFloppy].[Model] AS [hyperlink_Disk Model (Click to Google)]
,[tblFloppy].[Model] AS [hyperlink_name_Disk Model (Click to Google)]
,[tblFloppy].[SerialNumber] AS [SerialNumber]
FROM
[lansweeperdb].[dbo].[tblAssets]
INNER JOIN [lansweeperdb].[dbo].[tblAssetCustom] ON [tblAssets].[AssetID] = [tblAssetCustom].[AssetID]
INNER JOIN [lansweeperdb].[dbo].[tblFloppy] ON [tblAssets].[AssetID] = [tblFloppy].[AssetID]
WHERE
[tblAssets].[AssetType] = -1 -- -1 = "Windows"
AND [tblAssetCustom].[State] = 1 -- 1 = "Active"
ORDER BY
[tblAssets].[AssetName]
,[Disk Number]
Report 3: memory modules
Report contains the following information about memory modules:
- date of the last successful scan of a computer;
- computer name;
- last logged on user name;
- total capacity of the computer’s memory modules;
- number of memory modules in the computer;
- device locator — label of the socket that holds the memory;
- memory module form factor — DIMM, SODIMM or others;
- memory module type (DDR3 etc), capacity and speed;
- memory module manufacturer, model and serial number.
T-SQL query:
SELECT
CAST([tblAssets].[LastSeen] AS date) AS [Scan Date]
,'https://ls.adatum.local/asset.aspx?AssetID='
+ cast([tblAssets].[AssetID] AS nvarchar(100)) AS [hyperlink_Computer Name]
,[tblAssets].[AssetName] AS [hyperlink_name_Computer Name]
,'https://ls.adatum.local/user.aspx?username=' + [tblAssets].[Username]
+ '&userdomain=' + [tblAssets].[Userdomain] AS [hyperlink_Last Logged on User]
,[tblAssets].[Userdomain]
+ N'\' + [tblAssets].[Username] AS [hyperlink_name_Last Logged on User]
,CAST([tblAssets].[Memory] / 1024 AS int) AS [Total Memory (GB)]
,(
SELECT COUNT(*)
FROM [lansweeperdb].[dbo].[tblPhysicalMemory] AS [Inner]
WHERE [tblAssets].[AssetID] = [Inner].[AssetID]
) AS [Memory Modules Count]
,[tblPhysicalMemory].[DeviceLocator] AS [Device Locator]
,CAST([tblPhysicalMemory].[Capacity] / 1024 / 1024 / 1024 AS int) AS [Module Capacity (GB)]
,[tblPhysicalMemory].[Speed] AS [Max Speed (MHz)]
,[tblPhysicalMemory].[ConfiguredClockSpeed] AS [Configured Speed (MHz)]
,CASE [tblPhysicalMemory].[FormFactor]
WHEN N'0' THEN N'Unknown'
WHEN N'1' THEN N'Other'
WHEN N'2' THEN N'SIP'
WHEN N'3' THEN N'DIP'
WHEN N'4' THEN N'ZIP'
WHEN N'5' THEN N'SOJ'
WHEN N'6' THEN N'Proprietary'
WHEN N'7' THEN N'SIMM'
WHEN N'8' THEN N'DIMM'
WHEN N'9' THEN N'TSOP'
WHEN N'10' THEN N'PGA'
WHEN N'11' THEN N'RIMM'
WHEN N'12' THEN N'SODIMM'
WHEN N'13' THEN N'SRIMM'
WHEN N'14' THEN N'SMD'
WHEN N'15' THEN N'SSMP'
WHEN N'16' THEN N'QFP'
WHEN N'17' THEN N'TQFP'
WHEN N'18' THEN N'SOIC'
WHEN N'19' THEN N'LCC'
WHEN N'20' THEN N'PLCC'
WHEN N'21' THEN N'BGA'
WHEN N'22' THEN N'FPBGA'
WHEN N'23' THEN N'LGA'
END AS [Form Factor]
,CASE [tblPhysicalMemory].[MemoryType]
WHEN N'0' THEN N'Unknown'
WHEN N'1' THEN N'Other'
WHEN N'2' THEN N'DRAM'
WHEN N'3' THEN N'Synchronous DRAM'
WHEN N'4' THEN N'Cache DRAM'
WHEN N'5' THEN N'EDO'
WHEN N'6' THEN N'EDRAM'
WHEN N'7' THEN N'VRAM'
WHEN N'8' THEN N'SRAM'
WHEN N'9' THEN N'RAM'
WHEN N'10' THEN N'ROM'
WHEN N'11' THEN N'Flash'
WHEN N'12' THEN N'EEPROM'
WHEN N'13' THEN N'FEPROM'
WHEN N'14' THEN N'EPROM'
WHEN N'15' THEN N'CDRAM'
WHEN N'16' THEN N'3DRAM'
WHEN N'17' THEN N'SDRAM'
WHEN N'18' THEN N'SGRAM'
WHEN N'19' THEN N'RDRAM'
WHEN N'20' THEN N'DDR'
WHEN N'22' THEN N'DDR2 FB-DIMM'
WHEN N'24' THEN N'DDR3'
WHEN N'25' THEN N'FBD2'
END AS [Memory Type]
,[tblPhysicalMemory].[Manufacturer] AS [Manufacturer]
,'https://www.google.com/search?q='
+ [tblPhysicalMemory].[PartNumber] AS [hyperlink_Part Number (Click to Google)]
,[tblPhysicalMemory].[PartNumber] AS [hyperlink_name_Part Number (Click to Google)]
,[tblPhysicalMemory].[SerialNumber] AS [Serial Number]
FROM
[lansweeperdb].[dbo].[tblAssets]
INNER JOIN [lansweeperdb].[dbo].[tblAssetCustom] ON [tblAssets].[AssetID] = [tblAssetCustom].[AssetID]
LEFT JOIN [lansweeperdb].[dbo].[tblPhysicalMemory] ON [tblAssets].[AssetID] = [tblPhysicalMemory].[AssetID]
WHERE
[tblAssets].[AssetType] = -1 -- -1 = "Windows"
AND [tblAssetCustom].[State] = 1 -- 1 = "Active"
ORDER BY
[tblAssets].[AssetName]
Report 4: monitors
Report contains :
- date of the last successful scan of a computer;
- computer name;
- last logged on user name;
- number of monitors connected to the computer;
- monitor model and serial number.
T-SQL query:
SELECT
CAST([tblAssets].[LastSeen] AS date) AS [Scan Date]
,'https://ls.adatum.local/asset.aspx?AssetID='
+ cast([tblAssets].[AssetID] AS nvarchar(100)) AS [hyperlink_Computer Name]
,[tblAssets].[AssetName] AS [hyperlink_name_Computer Name]
,'https://ls.adatum.local/user.aspx?username=' + [tblAssets].[Username]
+ '&userdomain=' + [tblAssets].[Userdomain] AS [hyperlink_Last Logged on User]
,[tblAssets].[Userdomain] + N'\'
+ [tblAssets].[Username] AS [hyperlink_name_Last Logged on User]
,(
SELECT COUNT(*)
FROM [lansweeperdb].[dbo].[tblMonitor] AS [Inner]
WHERE [tblAssets].[AssetID] = [Inner].[AssetID]
) AS [Monitors Count]
,[tblMonitor].[MonitorManufacturer] AS [Monitor Manufacturer]
,'https://www.google.com/search?q='
+ [tblMonitor].[MonitorModel] AS [hyperlink_Monitor Model (Click to Google)]
,[tblMonitor].[MonitorModel] AS [hyperlink_name_Monitor Model (Click to Google)]
,[tblMonitor].[SerialNumber] AS [Monitor Serial Number]
,CAST([tblMonitor].[ManufacturedDate] AS date) AS [Monitor Manufactured Date]
FROM
[lansweeperdb].[dbo].[tblAssets]
INNER JOIN [lansweeperdb].[dbo].[tblAssetCustom] ON [tblAssets].[AssetID] = [tblAssetCustom].[AssetID]
LEFT JOIN [lansweeperdb].[dbo].[tblMonitor] ON [tblAssets].[AssetID] = [tblMonitor].[AssetID]
WHERE
[tblAssets].[AssetType] = -1 -- -1 = "Windows"
AND [tblAssetCustom].[State] = 1 -- 1 = "Active"
ORDER BY
[tblAssets].[AssetName]