→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jccrego
Engaged Sweeper II
I would like some help creating a report to include manually added assets (Not scanned). We have created a report with all of the criteria we want but, are unable to include any assets that were manually added and not scanned on the network. The manual assets have been updated to include the criteria below (tblAssetCustom.Department) as well as added to the appropriate Asset Groups. The manual assets may have any or all of the following fields:

State (active or non-active)
Asset Type (Windows, Apple Mac, Desktop, Laptop, or Unknown)
Assetname (unique name given to each manual asset)
Serial (unique S/N given to each manual asset)
Location
Building
Department
Asset groups


Our current report:


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADComputers.Description As [AD Description],
tblAssets.Username As [Last User],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Model,
tblOperatingsystem.Caption As [Operating System],
tblAssetCustom.PurchaseDate,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssetCustom.Building,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssetCustom.Custom4 As [Department Code],
tblAssetGroups.AssetGroup
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 tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.Department Like '%Football%' And
(tblAssetGroups.AssetGroup = 'Desktop' Or tblAssetGroups.AssetGroup =
'Mobile' Or tblAssetGroups.AssetGroup = 'Mobile Plus' Or
tblAssetGroups.AssetGroup = 'Performance' Or
tblAssetGroups.AssetGroup = 'Non-PP') And tblAssetCustom.State = 1
Order By tblAssets.AssetName
1 ACCEPTED SOLUTION
David_G
Lansweeper Employee
Lansweeper Employee
Most likely, the issue lies with the AD Computer and Operating system database tables being inner joined. The database tables tblADComputers and tblOperatingsystem only hold data that has been scanned. If they are inner joined, the report will only show results that also has scanned data from the aforementioned tables. To resolve this, you will need to Left Join them as I have done in the edited version of your report below.

Additionally, I have removed the filter to only show Active assets and have added the asset state to the report. Lastly, when filtering on asset groups, it is important to know that the database table tblAssetGroups only hold static asset groups. Dynamic groups can, unfortunately, not be reported on with custom reports, as these are views within your web console itself. So you might need to double-check if the groups you are filtering on are all static groups. nstructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADComputers.Description As [AD Description],
tblAssets.Username As [Last User],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Model,
tblOperatingsystem.Caption As [Operating System],
tblAssetCustom.PurchaseDate,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssetCustom.Building,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssetCustom.Custom4 As [Department Code],
tblAssetGroups.AssetGroup,
tblState.Statename As [Asset State]
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
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Department Like '%Football%' And
(tblAssetGroups.AssetGroup = 'Desktop' Or tblAssetGroups.AssetGroup = 'Mobile'
Or tblAssetGroups.AssetGroup = 'Mobile Plus' Or tblAssetGroups.AssetGroup =
'Performance' Or tblAssetGroups.AssetGroup = 'Non-PP')
Order By tblAssets.AssetName

View solution in original post

3 REPLIES 3
jccrego
Engaged Sweeper II
Thank you David G! That worked! We appreciate you taking the time to help with this! Consider this issue resolved.

Have a great day.


jccrego
David_G
Lansweeper Employee
Lansweeper Employee
Most likely, the issue lies with the AD Computer and Operating system database tables being inner joined. The database tables tblADComputers and tblOperatingsystem only hold data that has been scanned. If they are inner joined, the report will only show results that also has scanned data from the aforementioned tables. To resolve this, you will need to Left Join them as I have done in the edited version of your report below.

Additionally, I have removed the filter to only show Active assets and have added the asset state to the report. Lastly, when filtering on asset groups, it is important to know that the database table tblAssetGroups only hold static asset groups. Dynamic groups can, unfortunately, not be reported on with custom reports, as these are views within your web console itself. So you might need to double-check if the groups you are filtering on are all static groups. nstructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADComputers.Description As [AD Description],
tblAssets.Username As [Last User],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Model,
tblOperatingsystem.Caption As [Operating System],
tblAssetCustom.PurchaseDate,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssetCustom.Building,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssetCustom.Custom4 As [Department Code],
tblAssetGroups.AssetGroup,
tblState.Statename As [Asset State]
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
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Department Like '%Football%' And
(tblAssetGroups.AssetGroup = 'Desktop' Or tblAssetGroups.AssetGroup = 'Mobile'
Or tblAssetGroups.AssetGroup = 'Mobile Plus' Or tblAssetGroups.AssetGroup =
'Performance' Or tblAssetGroups.AssetGroup = 'Non-PP')
Order By tblAssets.AssetName
jccrego
Engaged Sweeper II
Any assistance on this would be greatly appreciated! Let me know if more information is needed or even if the answer exists elsewhere. I wasn't able to find any solutions with the existing filtering methods through the Report Center.

Thanks!
jccrego