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 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: 8  
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: 8  
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 Remove all users from old domain
by  cmuter   Go to last post Go to first unread
Last post: 9/20/2019 8:03:58 PM(UTC)
Lansweeper Static IP Address
by  cycleheat  
Go to last post Go to first unread
Last post: 9/20/2019 4:07:16 PM(UTC)
Lansweeper Bitlocker Encryption Recovery Key no information found
by  Stephane   Go to last post Go to first unread
Last post: 9/20/2019 2:26:19 PM(UTC)
Lansweeper InTune Scanning Issues
by  Esben.D  
Go to last post Go to first unread
Last post: 9/20/2019 12:34:59 PM(UTC)
Lansweeper Office 365 scanning issues
by  Esben.D   Go to last post Go to first unread
Last post: 9/20/2019 12:23:30 PM(UTC)
Lansweeper Hard Drive Tracking - Start to Finish
by  Lone Jedi  
Go to last post Go to first unread
Last post: 9/19/2019 8:11:56 PM(UTC)
Lansweeper Lansweeper Reporting Old Assets as New
by  Jpatterson   Go to last post Go to first unread
Last post: 9/19/2019 12:52:14 PM(UTC)
Lansweeper Cannot edit a ticket anymore
by  Esben.D  
Go to last post Go to first unread
Last post: 9/19/2019 12:32:42 PM(UTC)