Report for Asset Groups

[Mlacombe]

Hi,

First, I'd like to say what an awesome tool Lansweeper is. My company recently purchased it and I have been tasked with getting it ready to deploy out to our 7 sites. So far, so good.

With that said, I am looking for ways to modify some of the existing reports to display information based on which 'Asset Group' is assigned. I have 1 'Asset Group' for each site, and as part of the process of adding new machines, we will be assigning the asset to the appropriate 'Asset Group'. I have zero experience in this arena, and would very much appreciate some help. If I can get help fixing one report, I may be able to do the rest on my own.

As an example, say I want to modify the Workstations: Out of warranty in 30 days report to show the Workstations that will be out of Warranty in 30 days for Site A ONLY. Then a second report will have Workstations that will be out of Warranty in 30 days for Site B ONLY, etc. The end game here being to turn on alerts and have them emailed to the local tech at each site showing them only the data from their site.

Here is the existing code for this report, I'd appreciate it if someone could show me the correct parameters to add to make this report fit my needs.

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.Image As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblAssetCustom.Warrantydate < GetDate() + 30 And
tblAssetCustom.Warrantydate > GetDate()) And tblComputersystem.Domainrole < 2
And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc

Thank you for any help you can provide.

[Mlacombe]

Awesome, that worked like a charm. Thank you both!

[Hemoco]

Do the following:
- Double-click on tblAssetGroupLink in the table list on the right to add it to your report.
- Double-click on tblAssetGroups in the table list on the right to add it to your report.
- In the visual representation of the report in the upper section of the report builder, tick the AssetGroup field in tblAssetGroups to add it to your report.
- In the expression list, add the below criterion to the Criteria column for the tblAssetGroups.AssetGroup expression. Replace YourGroupName with the name of the group you would like to report on.

= 'YourGroupName'

[Mlacombe]

Thanks for the quick reply. Can you please give me a step by step on what exactly I need to do to get this functionality working?

Sorry, I'm very green at this!

[RCorbeil · Accepted solution]

The table linkage you want to use to retrieve each asset's group is:
tblAssets -> tblAssetGroupLink -> tblAssetGroups

Adding the two joins to your query, you can then retrieve the AssetGroup name and then set up your grouping/filters on it.

SELECT TOP 1000000
  tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tsysOS.Image As icon,
  tblAssetGroups.AssetGroup
FROM
  tblAssetCustom
  INNER JOIN tblAssets         ON tblAssetCustom.AssetID = tblAssets.AssetID
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  INNER JOIN tsysOS            ON tblAssets.OScode = tsysOS.OScode
  INNER JOIN tblAssetGroupLink ON tblAssets.AssetID = tblAssetGroupLink.AssetID
  INNER JOIN tblAssetGroups    ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
WHERE
  tblAssetCustom.State = 1
  AND tblComputersystem.Domainrole < 2
  AND ( tblAssetCustom.Warrantydate < GetDate() + 30
        AND tblAssetCustom.Warrantydate > GetDate()) 
ORDER BY
  [Warranty Expiration] Desc