Notification

Icon
Error

Pc's with SSD

Posted: Tuesday, August 19, 2014 5:52:55 PM(UTC)
spgs

spgs

Member Original PosterPosts: 8
0
Like
This issue has been solved! Click here to view the solution
Hi,
Can someone help me with a report , that show only the pcs without a ssd drive?

Thank you,
Fabio Tavares
SPGS
Lansweeper
#1Lansweeper Member Administration Posts: 13,442  
posted: 8/21/2014 3:00:40 PM(UTC)
There is no specific database field that identifies hard drives as solid state drives. You could try the report below (which marks drives whose model name includes "SSD" or "solid state" as solid state drives) to list the information you are after, but this report is not guaranteed to be accurate.
Quote:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblFloppy.AssetID
From tblFloppy Where (tblFloppy.Model Like '%ssd%') Or
(tblFloppy.Model Like '%solid%state%')) And tblAssets.Lastseen <> '' And
tblAssets.Lastseen Is Not Null And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
spgs
#2spgs Member Original PosterPosts: 8  
posted: 5/23/2018 12:36:10 PM(UTC)
Could not get this to work, so created own one that does work

Code:

Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblFloppy.Model,
tblAssetCustom.State
From tblFloppy
Inner Join tblAssets On tblFloppy.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (dbo.tblFloppy.Model Like N'%SSD%') Or
(dbo.tblFloppy.Model Like N'%solid%state%' And Not (dbo.tblAssets.Lastseen Is
Null) And dbo.tblAssetCustom.State = 1)


OR PCs WITHOUT SSD

Code:

Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblFloppy.Model,
tblAssetCustom.State
From tblFloppy
Inner Join tblAssets On tblFloppy.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where 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
Rick I
#3Rick I Member Posts: 5  
posted: 5/14/2019 8:15:01 PM(UTC)
So I used this report to show me which have SSD's and which don't and it seems to be working well as I go and swap older HD to SSD.... one thing I notice is it doesn't display kingston SSD as an SSD but as an HD.... how could I add that parameter to fix this report.

Originally Posted by: spgs Go to Quoted Post
Could not get this to work, so created own one that does work

Code:

Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblFloppy.Model,
tblAssetCustom.State
From tblFloppy
Inner Join tblAssets On tblFloppy.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (dbo.tblFloppy.Model Like N'%SSD%') Or
(dbo.tblFloppy.Model Like N'%solid%state%' And Not (dbo.tblAssets.Lastseen Is
Null) And dbo.tblAssetCustom.State = 1)


OR PCs WITHOUT SSD

Code:

Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblFloppy.Model,
tblAssetCustom.State
From tblFloppy
Inner Join tblAssets On tblFloppy.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where 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


Rick I
#4Rick I Member Posts: 5  
posted: 5/17/2019 6:21:48 PM(UTC)
How do I add the ability to see kingston as an SSD to this report? It is working well for me to see SSD except for a kingston drive

Active Discussions

Lansweeper How to modify scan schedule for multiple scanning targets?
by  aturkovich   Go to last post Go to first unread
Last post: Yesterday at 3:46:04 PM(UTC)
Lansweeper Changing/Editing tblComputerSystem.DomainRole
by  Esben.D  
Go to last post Go to first unread
Last post: Yesterday at 2:41:17 PM(UTC)
Lansweeper Version Check Page
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 2:37:43 PM(UTC)
Lansweeper warranty information missing
by  Esben.D  
Go to last post Go to first unread
Last post: Yesterday at 2:33:03 PM(UTC)
Lansweeper Dell Switch Servicetag
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 2:12:22 PM(UTC)
Lansweeper Office 365 ProPlus not detected in a few PCs
by  Esben.D  
Go to last post Go to first unread
Last post: Yesterday at 2:10:44 PM(UTC)
Lansweeper Imageright Installation
by  CyberCitizen   Go to last post Go to first unread
Last post: Yesterday at 1:01:30 AM(UTC)
Lansweeper Is the conditional step for OS arch broken?
by  CyberCitizen  
Go to last post Go to first unread
Last post: Yesterday at 1:00:54 AM(UTC)