Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-02-2014 07:31 AM
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?
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?
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-02-2014 05:13 PM
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.)
5 REPLIES 5
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-02-2014 09:29 PM
thank you all very much, that helped me a lot 🙂
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-02-2014 04:09 PM
I reviewed the report the other user sent and found errors. Try the one I created below:
This does require you have a purchase date filled out, otherwise it will display 0 for the days old column.
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-02-2014 02:27 PM
if i use this code i get a result of 0
it says:
"This report has no results!"
what could be wrong?
it says:
"This report has no results!"
what could be wrong?
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-02-2014 12:06 PM
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:
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-02-2014 05:13 PM
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.)