Drives with SSD Report

[andrewhancock91]

Hi, First post here. I'll start out by saying that I have little to no SQL experience so that is most likely why I'm having troubles! I'm trying to create a report to show machines that still have mechanical hard drives so I can start working on an upgrade schedule to either migrate them to an SSD or replace the machine altogether. Below is what I've come up with so far but doesn't seem to be working. Any thoughts are greatly appreciated, thanks!

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%')

[andrewhancock91]

I think we can make that work with the assumption of the physical drive relationship to C: drive, this report is mainly to target end user workstations with only one hard drive. The goal is to single out machines with mechanical hard drives and decide whether or not its worth upgrading to an SSD or replace the PC altogether.

Thank you for your help! I'm slowly but surely learning this SQL thing

[RCorbeil]

If you're certain of the mapping between the physical drive and the C: drive, you could fudge a relationship. Doing it this way is based on an assumption, so keep in mind what they say about "assume".

With the assumption caveat in mind, you could try something like this:

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%'
          )

If the simple assumption of physical drive 0 = C: doesn't hold true, you could expand the conditions to match your setup, e.g.

CASE
  WHEN AssetName = 'xxx' AND tblFloppy.Name LIKE '%DRIVE0' THEN 'C:'
  WHEN AssetName = 'yyy' AND tblFloppy.Name LIKE '%DRIVE1' THEN 'C:'
  etc.
END

It's a kludge, but it may do the trick for you.

[Esben_D]

I believe what you created is very close. There is no real distinction between a HDD and SSD aside from model name. The best way to filter for SSDs or filter them out is to use model name filters.

Here are some other topics I found with a quick search with similar reports:
https://www.lansweeper.com/forum/yaf_postst9503_Pcs-with-SSD.aspx#post37328
https://m.lansweeper.com/forum/yaf_postst11506_SSD-report.aspx#post42770
https://m.lansweeper.com/Forum/yaf_postst13630findunread_SSD-or-HDD-installed.aspx#post48315

One important thing to note (however you seem to have figured this out already) if you want physical hard drive information, it is stored in tblFloppy (I know the naming is weird) and not tblDiskdrives or tblDiskPartition.

[RCorbeil]

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%'
          )

[andrewhancock91]

RC62N, thank you for that it achieves what I was wanting with one exception. How do you add disk free space without using tblDiskdrives?

Thanks!

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%'
          )