→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎11-20-2017 11:02 PM
Select Distinct Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
Cast(Cast(tblAssets.Memory As bigint) / 1024 As Numeric) As RamGB,
tblAssets.Processor,
tblAssets.Username,
tblDiskdrives.FileSystem,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As
numeric) As FreeGB,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As
TotalSizeGB,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblDiskdrives.Caption,
tblAssets.OScode
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S'
And tblFloppy.Model Not Like 'OCZ%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like 'Kingston S%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like '%Solid State%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like '%SSD%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like '%NVMe%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like '%LITEON%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like '%MTFDDAV%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like '%Sandis%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like '%MZ-5S71000%') Or
(tblDiskdrives.Caption Like 'C:' And tblAssets.OScode Not Like '6.3.9600S' And
tblFloppy.Model Not Like '%pny%')
‎11-21-2017 07:59 PM
‎11-21-2017 07:06 PM
Select Top (1000000)
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
Cast(Cast(tblAssets.Memory As bigint) / 1024 As Numeric) As RamGB,
tblAssets.Processor,
tblAssets.Username,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.OScode,
tblFloppy.Name,
tblFloppy.Model,
Cast(Cast(tblFloppy.Size As bigint) / Power(10, 9) As numeric) As SizeGB,
Cast(Cast(tblFloppy.Size As bigint) / Power(2, 30) As numeric) As SizeGiB,
tblDiskdrives.Caption,
tblDiskdrives.FileSystem,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As TotalSizeGB
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner JOIN tblDiskdrives ON tblAssets.AssetID = tblDiskdrives.AssetID
AND tblDiskdrives.Caption = (CASE
WHEN tblFloppy.Name LIKE '%DRIVE0' THEN 'C:'
ELSE ''
END)
Where
tblAssets.OScode LIKE '6.3.9600S'
And NOT ( 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 '%NVMe%'
OR tblFloppy.Model LIKE '%LITEON%'
OR tblFloppy.Model LIKE '%MTFDDAV%'
OR tblFloppy.Model LIKE '%Sandis%'
OR tblFloppy.Model LIKE '%MZ-5S71000%'
OR tblFloppy.Model LIKE '%pny%'
)
CASE
WHEN AssetName = 'xxx' AND tblFloppy.Name LIKE '%DRIVE0' THEN 'C:'
WHEN AssetName = 'yyy' AND tblFloppy.Name LIKE '%DRIVE1' THEN 'C:'
etc.
END
‎11-21-2017 01:10 PM
‎11-21-2017 12:21 AM
Select Top (1000000)
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
Cast(Cast(tblAssets.Memory As bigint) / 1024 As Numeric) As RamGB,
tblAssets.Processor,
tblAssets.Username,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.OScode,
tblFloppy.Name,
tblFloppy.Model,
Cast(Cast(tblFloppy.Size As bigint) / Power(10, 9) As numeric) As SizeGB,
Cast(Cast(tblFloppy.Size As bigint) / Power(2, 30) As numeric) As SizeGiB
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where
tblAssets.OScode LIKE '6.3.9600S'
And NOT ( 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 '%NVMe%'
OR tblFloppy.Model LIKE '%LITEON%'
OR tblFloppy.Model LIKE '%MTFDDAV%'
OR tblFloppy.Model LIKE '%Sandis%'
OR tblFloppy.Model LIKE '%MZ-5S71000%'
OR tblFloppy.Model LIKE '%pny%'
)
‎11-21-2017 04:00 PM
RC62N wrote:
I think part of the problem you're running into is that there isn't a direct relationship between tblDiskdrives and tblFloppy. Each table can be connected to tblAssets, but they aren't connected to one another.
Drop tblDiskdrives from your query.
Add tblFloppy.Name and tblFloppy.Size. If you only want the primary drive, you can add a filter for tblFloppy.Name LIKE '%DRIVE0'. In theory, C: will be on physical drive 0, but it doesn't have to be, so you may want to hold off on that filter until you see your results.
Something like this should get you in the ballpark:Select Top (1000000)
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
Cast(Cast(tblAssets.Memory As bigint) / 1024 As Numeric) As RamGB,
tblAssets.Processor,
tblAssets.Username,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.OScode,
tblFloppy.Name,
tblFloppy.Model,
Cast(Cast(tblFloppy.Size As bigint) / Power(10, 9) As numeric) As SizeGB,
Cast(Cast(tblFloppy.Size As bigint) / Power(2, 30) As numeric) As SizeGiB
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where
tblAssets.OScode LIKE '6.3.9600S'
And NOT ( 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 '%NVMe%'
OR tblFloppy.Model LIKE '%LITEON%'
OR tblFloppy.Model LIKE '%MTFDDAV%'
OR tblFloppy.Model LIKE '%Sandis%'
OR tblFloppy.Model LIKE '%MZ-5S71000%'
OR tblFloppy.Model LIKE '%pny%'
)
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now