Pc's with SSD

[spgs]

Hi,
Can someone help me with a report , that show only the pcs without a ssd drive?

Thank you,
Fabio Tavares
SPGS

[SecqureIT]

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

[Hemoco]

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.

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 · Accepted solution]

Could not get this to work, so created own one that does work

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

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

[SecqureIT]

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.

spgs wrote:
Could not get this to work, so created own one that does work

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

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