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

View solution in original post

4 REPLIES 4
Mlacombe
Engaged Sweeper III
Awesome, that worked like a charm. Thank you both!
Hemoco
Lansweeper Alumni
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
Engaged Sweeper III
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
Honored Sweeper II
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now