Age of hardware

[makatp]

Hi,


i´m searching for a way to figure out the age of hardware used by employees. my report should show me:

- assetname
- Fullname of User regarding description field of AD
- IP-Adress of client
- Type of Hardware (Nb or WS)
- Age of hardware
- Brand of hardware

is this possible with report builder?

[makatp]

thank you all very much, that helped me a lot 🙂

[sukaitsu]

I reviewed the report the other user sent and found errors. Try the one I created below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  Case
    When tblAssetCustom.PurchaseDate Is Not Null Then DateDiff(day,
    tblAssetCustom.PurchaseDate, GetDate()) Else '0' End As [Days Old],
  tblAssets.Userdomain + '\' + tblAssets.Username As [Last User],
  tblADusers.Name,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole <= 1

This does require you have a purchase date filled out, otherwise it will display 0 for the days old column.

[makatp]

if i use this code i get a result of 0

it says:

"This report has no results!"

what could be wrong?

[mott]

Hi,

I have a similar report, but in the first step I was forced to group my assets into Assetgroups (Desktop, Laptop). Then this report will help:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetGroups.AssetGroup,
  tblAssetCustom.PurchaseDate,
  DateDiff(yy, tblAssetCustom.PurchaseDate, GetDate()) As Age,
  tblAssetCustom.Manufacturer
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblAssetGroups.AssetGroup = 'Desktop' Or tblAssetGroups.AssetGroup =
    'Laptop') And tblAssetCustom.State = 1

[RCorbeil · Accepted solution]

mott wrote:
I have a similar report, but in the first step I was forced to group my assets into Assetgroups (Desktop, Laptop).

Instead of manually assigning asset groups, just check whether the machine has a portable battery.

Add as a field:

  CASE
    WHEN tblPortableBattery.Manufacturer IS NOT NULL
    THEN 'Notebook'
    ELSE 'Desktop'
  END As Class,

And add to the list of tables you're selecting from:

  LEFT JOIN tblPortableBattery ON tblPortableBattery.AssetID = tblAssets.AssetID

(Note the LEFT join, not the default INNER join. If you INNER join, you'll filter your result set for only machines that have a portable battery.)