cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tamirt
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
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

View solution in original post

1 REPLY 1
Nick_VDB
Champion Sweeper III
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

New to Lansweeper?

Try Lansweeper For Free

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

Try Now