Report to include manually added assets

[jccrego]

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

[jccrego]

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 · Accepted solution]

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]

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