Reports for Stock

[tamirt]

Hi

I have been looking for and i can't find the most needed Reports

1. list all the items that are in the Stock state.
2. List all the items that in a Group that i have made with a name that i have given it like (IT LAB)
3. List all the items that in a Domain. now this has a reports but it will not work if the device is not on
the Active state and i need it to work for units that are also in the stock state

now also how can i create a new reports with what i want , is there a tool that can help me with this task
and if there is no tool
and you will just send me the code for what i have ask then i also want to know how do i use it so that it will be add to the list of reports that i have in the system.

Thk
Tamir

[Nick_VDB · Accepted solution]

We have added three reports below.

Instructions for adding these reports 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.

1. list all the items that are in the Stock state.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tblState.Statename As State,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Stock'
Order By tblAssets.AssetName

2. List all the items that in a Group that i have made with a name that i have given it like (IT LAB)

We presume that you are referring to an asset group that you have created (not dynamic asset group). You can replace the highlighted code with the asset group that you want the report to give back.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetGroups.AssetGroup,
  tblAssets.Lastseen,
  tblAssets.Lasttried
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
Where tblAssetGroups.AssetGroup = 'IT LAB'
Order By tblAssets.AssetName

3. List all the items that in a Domain. now this has a reports but it will not work if the device is not on
the Active state and i need it to work for units that are also in the stock state

We presume that you are referring to the report Computer: Part Of Domain. We added a modified version below that will give back any assets part of a domain no matter the state it is set to.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblState.Statename As State,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblComputersystem.PartOfDomain = 1
Order By tblAssets.AssetName