Notification

Icon
Error

Drives with SSD Report - Trying to create a report of all machines with mechanical HDD's still

Posted: Monday, November 20, 2017 11:02:27 PM(UTC)
andrewhancock91

andrewhancock91

Member Original PosterPosts: 7
0
Like
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!

Quote:
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%')
RC62N
#1RC62N Member Posts: 387  
posted: 11/21/2017 12:21:08 AM(UTC)
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:
Code:
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%'
          )
Esben.D
#2Charlie.X Member Administration Posts: 1,933  
posted: 11/21/2017 1:10:12 PM(UTC)
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.c...-with-SSD.aspx#post37328
https://m.lansweeper.com...SD-report.aspx#post42770
https://m.lansweeper.com...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.

andrewhancock91
#3andrewhancock91 Member Original PosterPosts: 7  
posted: 11/21/2017 4:00:19 PM(UTC)
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!

Originally Posted by: RC62N Go to Quoted Post
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:
Code:
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%'
          )


RC62N
#4RC62N Member Posts: 387  
posted: 11/21/2017 7:06:21 PM(UTC)
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:
Code:
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.
Code:
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.
andrewhancock91
#5andrewhancock91 Member Original PosterPosts: 7  
posted: 11/21/2017 7:59:42 PM(UTC)
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

Active Discussions

Lansweeper Hyper-V guests dissapeared and reappeared
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 4:30:47 PM(UTC)
Lansweeper DB cleanup script
by  William382  
Go to last post Go to first unread
Last post: Yesterday at 4:23:43 PM(UTC)
Lansweeper Installing MS KB with Deploy
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 4:01:45 PM(UTC)
Lansweeper Ticket Info Meter incorrect
by  pfalls  
Go to last post Go to first unread
Last post: Yesterday at 3:27:44 PM(UTC)
Lansweeper Asset Checkboxes in reports
by  ufficioced   Go to last post Go to first unread
Last post: Yesterday at 1:22:17 PM(UTC)
Lansweeper Silent "Run as logged in user" option
by  CyberCitizen  
Go to last post Go to first unread
Last post: Yesterday at 3:45:25 AM(UTC)
Lansweeper New ticket creation not emailing the user
by  MVMIC IT LANSWEEPER   Go to last post Go to first unread
Last post: 11/15/2019 5:36:23 PM(UTC)