‎09-04-2015 05:10 PM
Solved! Go to Solution.
‎09-04-2015 07:42 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblFloppy.InterfaceType As [HD interface],
tblFloppy.Manufacturer As [HD manufacturer],
tblFloppy.Model As [HD model],
tblFloppy.Name,
Cast(Cast(tblFloppy.Size As bigint) / 1024 / 1024 / 1024 As numeric)
As [HD Size (GB)],
tblFloppy.SerialNumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblFloppy.Model Like '%ssd%' Or tblFloppy.Model Like '%solid-state%') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName,
tblFloppy.Name
‎04-21-2023 07:45 AM - edited ‎05-16-2023 07:32 AM
Latest version:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.Image As icon,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Userdomain,
tblAssets.Username,
tblAssets.Memory,
tblAssets.Processor,
tblProcessor.NumberOfCores * tblAssets.NrProcessors As TotalCores,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.IPAddress As [IP Address],
tblAssets.Lastseen,
tblADusers.Title,
tblADusers.Department,
tblFloppy.Model As SSDModel,
tblFloppy.FirmwareRevision,
tblFloppy.SerialNumber,
tblFloppy.Size,
tblFloppy.InterfaceType
From tblAssets
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID And
tblFloppy.InterfaceType <> 'USB' And (tblFloppy.Model Like 'OCZ%' Or
tblFloppy.Model Like '%TL100%' Or tblFloppy.Model Like 'Plextor PX%' Or
Replace(tblFloppy.Model, ' ', '') Like 'KingstonS%' Or
tblFloppy.Model Like '%Solid%' Or tblFloppy.Model Like '%Patrio%' Or
tblFloppy.Model Like '%udinfo%' Or tblFloppy.Model Like '%THNSN%' Or
tblFloppy.Model Like '%NVMe%' Or tblFloppy.Model Like '%MZ%' Or
tblFloppy.Model Like '% run %' Or tblFloppy.Model Like '% meta %' Or
tblFloppy.Model Like '% mega %' Or tblFloppy.Model Like '%MTFD%' Or
tblFloppy.Model Like '%amd %' Or tblFloppy.Model Like 'SanDisk%' Or
tblFloppy.Model Like 'NE-%' Or tblFloppy.Model Like '%smartbuy%' Or
tblFloppy.Model Like 'PH_-%' Or tblFloppy.Model Like 'gigabyte %' Or
tblFloppy.Model Like 'Apacer%' Or Replace(tblFloppy.Model, ' ',
'') Like 'ADATA%' Or Replace(tblFloppy.Model, ' ', '') Like 'ToshibaA%'
Or tblFloppy.Model Like 'SanDis SD%' Or tblFloppy.Model Like 'WDC WDS%'
Or Replace(tblFloppy.Model, ' ', '') Like '%SSD%')
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username
Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Order By tblAssets.AssetID
‎03-24-2017 02:44 PM
‎03-24-2017 10:27 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Lastseen,
tblFloppy.InterfaceType As [HD interface],
tblFloppy.Manufacturer As [HD manufacturer],
tblFloppy.Model As [HD model],
tblFloppy.Name,
Cast(Cast(tblFloppy.Size As bigint) / 1024 / 1024 / 1024 As numeric)
As [HD Size (GB)],
tblFloppy.SerialNumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where ((tblFloppy.Model Like '%SSD%') Or (tblFloppy.Model Like '%Solid State%')
Or (tblFloppy.Model Like '%OADATA S%') Or
(tblFloppy.Model Like '%ADTDTA XPG%') Or (tblFloppy.Model Like '%ADTron X%')
Or (tblFloppy.Model Like '%Asus Phison%') Or (tblFloppy.Model Like
'%Buffalo Silicon%') Or (tblFloppy.Model Like '%Corsair P128%') Or
(tblFloppy.Model Like '%Corsair Extreme%') Or
(tblFloppy.Model Like '%Corsair Reactor%') Or
(tblFloppy.Model Like '%Crucial CT%') Or
(tblFloppy.Model Like '%CSX Memory%') Or
(tblFloppy.Model Like '%G.Skill Falcon%') Or (tblFloppy.Model Like
'%G.Skill FM%') Or (tblFloppy.Model Like '%G.Skill Titan%') Or
(tblFloppy.Model Like '%Indilinx Barefoot%') Or
(tblFloppy.Model Like '%Indilinx Everest%') Or
(tblFloppy.Model Like '%Intel%') Or (tblFloppy.Model Like '%JMicron JMF%')
Or (tblFloppy.Model Like '%KingSpec KSD%') Or
(tblFloppy.Model Like '%Kingston HyperX%') Or
(tblFloppy.Model Like '%Kingston S%') Or (tblFloppy.Model Like '%M4-CT%') Or
(tblFloppy.Model Like '%Marvell 88SS%') Or (tblFloppy.Model Like '%MTFD%')
Or (tblFloppy.Model Like '%MZ%') Or (tblFloppy.Model Like '%MTron MSD%') Or
(tblFloppy.Model Like '%OCZ%') Or (tblFloppy.Model Like '%Patriot Inferno%')
Or (tblFloppy.Model Like '%Patriot Warp%') Or
(tblFloppy.Model Like '%Patriot Memory%') Or (tblFloppy.Model Like
'%Patriot Torqx%') Or (tblFloppy.Model Like '%Patriot Zephyr%') Or
(tblFloppy.Model Like '%Phison PS3%') Or (tblFloppy.Model Like '%RunCore%')
Or (tblFloppy.Model Like '%Samsung 810%') Or (tblFloppy.Model Like
'%Samsung 830%') Or (tblFloppy.Model Like '%Samsung 840%') Or
(tblFloppy.Model Like '%Samsung 850%') Or
(tblFloppy.Model Like '%Samsung 950%') Or
(tblFloppy.Model Like '%Samsung 951%') Or
(tblFloppy.Model Like '%SAMSUNG MMC%') Or
(tblFloppy.Model Like '%Samsung NVMe%') Or
(tblFloppy.Model Like '%Sandforce SF') Or
(tblFloppy.Model Like '%SanDisk SD%%') Or
(tblFloppy.Model Like '%SILICONMOTION SM%') Or
(tblFloppy.Model Like '%Silicon Power%') Or (tblFloppy.Model Like
'%Stec Pata%') Or (tblFloppy.Model Like 'SuperTalent MasterDrive%') Or
(tblFloppy.Model Like '%TL100%') Or (tblFloppy.Model Like '%THNSN%') Or
(tblFloppy.Model Like 'Toshiba T6UG1XBG%') Or
(tblFloppy.Model Like 'Toshiba TC58NCF%') Or (tblFloppy.Model Like
'Transcend%')) And tblAssetCustom.State = 1
Order By [HD model],
tblFloppy.Name
‎03-24-2017 05:26 AM
(
(tblFloppy.Model Like 'OCZ%')
Or (tblFloppy.Model Like '%TL100%')
Or (tblFloppy.Model Like 'Kingston S%')
Or (tblFloppy.Model Like '%Solid%')
Or (tblFloppy.Model Like '%SSD%')
Or (tblFloppy.Model Like '%THNSN%')
Or (tblFloppy.Model Like '%NVMe%')
Or (tblFloppy.Model Like '%MZ%')
Or (tblFloppy.Model Like '%MTFD%')
Or (tblFloppy.Model Like 'Plextor PX%')
Or (tblFloppy.Model Like 'SanDisk S%')
)
‎03-23-2017 04:39 PM
‎03-23-2017 03:55 PM
Where
(tblFloppy.Model Like 'OCZ%')
OR (tblFloppy.Model Like 'Kingston S%')
OR (tblFloppy.Model Like '%Solid State%')
OR (tblFloppy.Model Like '%SSD%')
OR (tblFloppy.Model Like '%MZNLN%')
OR (tblFloppy.Model Like '%MZFLV%')
OR (tblFloppy.Model Like '%MZ7LN%')
OR (tblFloppy.Model Like '%THNSN%')
OR (tblFloppy.Model Like '%TL100%')
OR (tblFloppy.Model Like '%NVMe%' And tblAssetCustom.State = 1)
Where
(tblAssetCustom.State = 1) AND
( (tblFloppy.Model Like 'OCZ%')
OR (tblFloppy.Model Like 'Kingston S%')
OR (tblFloppy.Model Like '%Solid State%')
OR (tblFloppy.Model Like '%SSD%')
OR (tblFloppy.Model Like '%MZNLN%')
OR (tblFloppy.Model Like '%MZFLV%')
OR (tblFloppy.Model Like '%MZ7LN%')
OR (tblFloppy.Model Like '%THNSN%')
OR (tblFloppy.Model Like '%TL100%')
OR (tblFloppy.Model Like '%NVMe%')
)
‎03-23-2017 03:48 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblFloppy.InterfaceType As [HD interface],
tblFloppy.Manufacturer As [HD manufacturer],
tblFloppy.Model As [HD model],
tblFloppy.Name,
Cast(Cast(tblFloppy.Size As bigint) / 1024 / 1024 / 1024 As numeric)
As [HD Size (GB)],
tblFloppy.SerialNumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where
(tblAssetCustom.State = 1) AND
( (tblFloppy.Model Like 'OCZ%')
OR (tblFloppy.Model Like 'Kingston S%')
OR (tblFloppy.Model Like '%Solid State%')
OR (tblFloppy.Model Like '%SSD%')
OR (tblFloppy.Model Like '%MZNLN%')
OR (tblFloppy.Model Like '%MZFLV%')
OR (tblFloppy.Model Like '%MZ7LN%')
OR (tblFloppy.Model Like '%THNSN%')
OR (tblFloppy.Model Like '%TL100%')
OR (tblFloppy.Model Like '%NVMe%')
)
Order By tblAssets.AssetName,
tblFloppy.Name
‎09-30-2016 06:40 AM
‎09-30-2016 12:15 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now