cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
spgs
Engaged Sweeper II
Hi,
Can someone help me with a report , that show only the pcs without a ssd drive?

Thank you,
Fabio Tavares
SPGS
1 ACCEPTED SOLUTION
spgs
Engaged Sweeper II
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

View solution in original post

4 REPLIES 4
SecqureIT
Engaged Sweeper II
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
Lansweeper Alumni
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
Engaged Sweeper II
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
Engaged Sweeper II
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