cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
makatp
Engaged Sweeper
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?
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.)

View solution in original post

5 REPLIES 5
makatp
Engaged Sweeper
thank you all very much, that helped me a lot 🙂
sukaitsu
Champion Sweeper
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.
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com
makatp
Engaged Sweeper
if i use this code i get a result of 0

it says:

"This report has no results!"

what could be wrong?
mott
Engaged Sweeper II
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
Honored Sweeper II
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.)