cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tamirt
Engaged Sweeper II
Hi

I need to make these two report

1. username | computer name | model | barcode | sn| purchasing date | end of warranty date

now i need one for computers and one for servers
2 REPLIES 2
tamirt
Engaged Sweeper II
Thk for the report but I do have a problem with the server one as it will not give me any info on my physical servers it only list all my VMware machine
so if you can have a look and fix it this will help

p.s
I am not an SQL guy so this is way I need your help
David_G
Lansweeper Employee
Lansweeper Employee
I've made two reports, one for workstations and one for servers with the information you are after. Instructions 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.
Workstations
Select Top 1000000 tblAssets.Username,
tblAssets.Userdomain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.BarCode,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Where tblState.Statename = 'Active' And tblDomainroles.Domainrolename In
('Stand-alone Workstation', 'Member Workstation')
Order By tblAssets.Username,
tblAssets.Domain,
tblAssets.AssetName

Servers
Select Top 1000000 tblAssets.Username,
tblAssets.Userdomain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.BarCode,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Where tblState.Statename = 'Active' And tblDomainroles.Domainrolename In
('Stand-alone server', 'Member server', 'Primary domain controller',
'Backup domain controller')
Order By tblAssets.Username,
tblAssets.Domain,
tblAssets.AssetName