list disks with free space and a specific software installed

[glesax]

Had this question up on a different account because i couldn't find the login details for this one. Now posted with the correct account


Hi,

I would need some help with a custom querie. We are to distribute a software but need to know computers that might have to little disk space free. Some, not all coputers also have a secondary disk. We need to know incase there is a secondary disk so that we might do a swap or install on that disk insted.

I would need a list:
Computer name, size of C, Free space C, Size of D, Free space D, Specific installed software

The problem im having is getting the disk in separate colums and also only show computers with the desired software. With the disks having own colums its easier to do filtering in excel. Any help would be nice.

//Pekka

[glesax]

That is exactly what I needed!

Thanks for the help and fast reply. This one will save us a lot of problems.

Best Regards//
Pekka

[Hemoco · Accepted solution]

Please use the following report, you just have to replace the word 'name software' with the name of the software that you want to filter on:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Convert(Decimal(7,2),q1.Freespace / 1024 / 1024 / 1024) As 'c free space',
Convert(Decimal(7,2),q1.Size / 1024 / 1024 / 1024) As 'c size',
Convert(Decimal(7,2),q2.Freespace / 1024 / 1024 / 1024) As 'd free space',
Convert(Decimal(7,2),q2.Size / 1024 / 1024 / 1024) As 'd size',
Case When q3.AssetID Is Null Then 'not installed' Else 'installed'
End As 'software installed'
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Join (Select tblDiskdrives.AssetID,
tblDiskdrives.Caption,
tblDiskdrives.Freespace,
tblDiskdrives.Size
From tblDiskdrives
Where tblDiskdrives.Caption Like '%c%') q1 On q1.AssetID = tblAssets.AssetID
Left Join (Select tblDiskdrives.AssetID,
tblDiskdrives.Caption,
tblDiskdrives.Freespace,
tblDiskdrives.Size
From tblDiskdrives
Where tblDiskdrives.Caption Like '%d%') q2 On q2.AssetID = tblAssets.AssetID
Left Join (Select tblSoftware.AssetID
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%name software%') q3 On tblAssets.AssetID =
q3.AssetID

To use the report above, do the following:
• Open the report builder under Reports/Create New Report.
• Paste the SQL code we provided at the bottom of the page.
• Left-click somewhere in the upper section of the page so the code applies.
• Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.