→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jproust
Engaged Sweeper
Hi,

I would like the same report with Dynamic Groups which I customized by group. For example, I grouped laptop, portable, notebook by "Laptops". And if it's possible to count them, it will be perfect 😉

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
TsysChassisTypes.ChassisName,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblADusers.Department
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where (TsysChassisTypes.ChassisName = 'Laptop' Or TsysChassisTypes.ChassisName =
'Notebook' Or TsysChassisTypes.ChassisName = 'Portable' Or
TsysChassisTypes.ChassisName = 'Desktop')
Order By TsysChassisTypes.ChassisName Desc
3 REPLIES 3
RCorbeil
Honored Sweeper II
According to the response here, in June 2015 it was not possible to create reports based on dynamic asset groups. I'm assuming that continues to be the case.
RCorbeil
Honored Sweeper II
On the other hand, if you're saying that you've defined dynamic groups and you're wanting to report based on them, in theory something like this should work:
Select Top 1000000
...
tblAssetGroups.AssetGroup,
...
From
...
Inner Join tblAssetGroupLink On tblAssetGroupLink.AssetID = tblAssets.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where
tblAssetGroups.Dynamic = 1
AND tblAssetGroups.AssetGroup IN ('Desktop', 'Laptop')
Order By
tblAssetGroups.AssetGroup,
TsysChassisTypes.ChassisName Desc

I say "in theory" because when I run a report against asset groups, I get precisely zero dynamic groups reported despite having a few defined. Static groups show up fine, just no dynamic.
RCorbeil
Honored Sweeper II
If I'm correctly reading what you wrote, you're just looking for a way of creating your own category name for a selection of chassis types, right? If so, add the following CASE to your query:
  CASE
WHEN TsysChassisTypes.ChassisName IN ('Desktop',
'Low Profile',
'Mini Tower',
'Tower',
'Space-Saving',
'All in One',
'Mini PC',
'Pizza Box',
'Sealed-Case') THEN 'Desktop'
WHEN TsysChassisTypes.ChassisName IN ('Portable',
'Laptop',
'Notebook',
'Convertible',
'Sub Notebook',
'Hand Held',
'Lunch Box') THEN 'Laptop'
ELSE 'Other'
END AS ChassisGroup,
and add the new field to your ORDERing.

I populated the CASE values from the contents of TsysChassisTypes. Unless you're certain that you don't have (or won't someday have) any of the other ChassisName values, you may want to consider expanding your WHERE filter.

Speaking of the WHERE clause, you can simplify it if you like:
Where
TsysChassisTypes.ChassisName IN ('Laptop', 'Notebook', 'Portable', 'Desktop')

As far as counting them,
Select Top 1000000
COUNT(*) AS [Count],
CASE
WHEN TsysChassisTypes.ChassisName IN ('Desktop',
'Low Profile',
'Mini Tower',
'Tower',
'Space-Saving',
'All in One',
'Mini PC',
'Pizza Box',
'Sealed-Case') THEN 'Desktop'
WHEN TsysChassisTypes.ChassisName IN ('Portable',
'Laptop',
'Notebook',
'Convertible',
'Sub Notebook',
'Hand Held',
'Lunch Box') THEN 'Laptop'
ELSE 'Other'
END AS [ChassisGroup]
From
tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Where
TsysChassisTypes.ChassisName IN ('Laptop', 'Notebook', 'Portable', 'Desktop')
Group By
CASE
WHEN TsysChassisTypes.ChassisName IN ('Desktop',
'Low Profile',
'Mini Tower',
'Tower',
'Space-Saving',
'All in One',
'Mini PC',
'Pizza Box',
'Sealed-Case') THEN 'Desktop'
WHEN TsysChassisTypes.ChassisName IN ('Portable',
'Laptop',
'Notebook',
'Convertible',
'Sub Notebook',
'Hand Held',
'Lunch Box') THEN 'Laptop'
ELSE 'Other'
END
should do the trick.