Out of Warranty report with more information

[Sean_m_Taylor]

I attempted to create an out of warranty report that includes what CPU, free HDD space, and how much memory each computer has. The thing I created works....sort of except it duplicates everything two or three times.

Can anyone help?

*By create I mean I took an existing report and edited it*


This is the current report


Select Top 1000000 tsysOS.Image As icon,
tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.Processor,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) As [%SpaceLeft],
tblDiskdrives.Lastchanged As LastChanged
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where (tblAssetCustom.PurchaseDate Is Not Null And tblAssetCustom.State = 1) Or
(tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

[endyk]

I agree with RC62N. My solution works only if you want to report on the most recently changed drive which probably is unlikely. Nonetheless, I believe you have something to get you going. Let us know if you need further clarification.

[RCorbeil]

More likely is that you're reporting on all the drives -- hard drives, floppy drives, optical drives, etc. -- where I assume you're after either only the C: drive or only the hard drives. To verify, add tblDiskdrives.Caption to your report and you'll see each drive's letter (e.g. "C:").

If all you want to see is the C: drive, add a filter for tblDiskdrives.Caption = 'C:'. If you want to see all local hard drives (including USB-attached), filter for tblDiskdrives.DriveType = 3. (See the database documentation for the drive type definitions.)

[endyk · Accepted solution]

The last changed date in tblDiskdrives is causing the duplicate. You will want to get the max lastchange date details for each asset from the table. Below is an updated query that will work.

Select Top 1000000 tsysOS.Image As icon,
tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.Processor,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) As [%SpaceLeft],
tblDiskdrives.Lastchanged
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join (select dd.*
from tblDiskdrives dd
inner join (select distinct AssetID,max(Lastchanged) as Lastchanged from tblDiskdrives group by AssetID) lc on lc.AssetID = dd.AssetID and lc.Lastchanged = dd.Lastchanged) tblDiskdrives on tblAssets.AssetID = tblDiskdrives.AssetID
Where (tblAssetCustom.PurchaseDate Is Not Null And tblAssetCustom.State = 1) Or
(tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

[Sean_m_Taylor]

endyk wrote:
The last changed date in tblDiskdrives is causing the duplicate. You will want to get the max lastchange date details for each asset from the table. Below is an updated query that will work.

Select Top 1000000 tsysOS.Image As icon,
tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.Processor,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) As [%SpaceLeft],
tblDiskdrives.Lastchanged
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join (select dd.*
from tblDiskdrives dd
inner join (select distinct AssetID,max(Lastchanged) as Lastchanged from tblDiskdrives group by AssetID) lc on lc.AssetID = dd.AssetID and lc.Lastchanged = dd.Lastchanged) tblDiskdrives on tblAssets.AssetID = tblDiskdrives.AssetID
Where (tblAssetCustom.PurchaseDate Is Not Null And tblAssetCustom.State = 1) Or
(tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

Thank you, this did exactly what I was trying to do.