Trying to duplicate this report

[chaley]

I'm trying to duplicate the report I found in another thread:

Device Name
type(desktop, laptop)
Manufacturer
Model
IP location
OS + version
Install Date
servicepack
OS serial
User(lastlogin)
Domain
Serialnumber
Disk volumes
Memory
Processor
Network(+ip info)
Other Hardware info
Software installed

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, TsysChassisTypes.ChassisName,
tblComputersystem.Manufacturer, tblComputersystem.Model,
web40AllIPLocations.IPLocation, Web40OSName.OSname, Web40OSName.OScode,
tblOperatingsystem.InstallDate, Web40OSName.SP,
tblOperatingsystem.SerialNumber, tblComputers.Username,
tblComputers.Userdomain, tblSystemEnclosure1.SerialNumber As SerialNumber1,
tblDiskdrives.Caption, Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 /
1024 As numeric) As Free, Cast(Cast(tblDiskdrives.Size As bigint) / 1024 /
1024 As numeric) As Total, web40CorrectMemory.Memory,
web40ProcessorCapacity.NrOfProcessors, web40ProcessorCapacity.CPU,
tblComputers.LastknownIP, tblSoftware.softwareName,
tblSoftware.softwareVersion, tblSoftware.SoftwarePublisher
From tblComputers Left Join
tblSystemEnclosure On tblComputers.Computername =
tblSystemEnclosure.Computername Inner Join
TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes Left Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername Left Join
web40AllIPLocations On web40AllIPLocations.Computername =
tblComputers.Computername Left Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Left Join
tblSystemEnclosure tblSystemEnclosure1 On tblComputers.Computername =
tblSystemEnclosure1.Computername Left Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Left Join
web40CorrectMemory On web40CorrectMemory.Computername =
tblComputers.Computername Left Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername Inner Join
tblDiskdrives On tblComputers.Computername = tblDiskdrives.Computername
Where tblDiskdrives.DriveType = 3
Order By tblComputers.ComputerUnique, tblSoftware.softwareName,
tblSoftware.softwareVersion


However I keep getting errors that some of the tables dont exist such as tblComputers. I tried to rename it, but ran into other errors. Would it be possible to get an updated version of this report?

I would also like to add installed drives such as CD's and Floppy's if possible.


Thank you for your help and providing such a great tool.

Charles

[Hemoco]

A sample tblFloppy report was posted here. For the tblCDROMDrive report:
- Open the report builder under Dashboard\Reports\Report Builder.
- Hit the Default SQL button.
- Double-click on tblCDROMDrive in the list on the right to add it to the report.
- In the visual representation of tblCDROMDrive in the upper section of the report builder, tick the fields you need.
- Hit the Save & Run button.

[chaley]

Would you be able to help with the report for the CDROM/Floppy and attached peripherals as well?

[Hemoco · Accepted solution]

chaley wrote:
However I keep getting errors that some of the tables dont exist such as tblComputers. I tried to rename it, but ran into other errors.

You are trying to run a 4.2 report in Lansweeper 5.0, which will not work. Lansweeper 5.0 has a different database structure. A sample 5.0 report can be seen below. We did not include software information, as including both software and disks will lead to duplication. For software, use the built-in report “Software: List of software by computer”. Most notable 5.0 database changes:
Tblcomputers -> tblassets
Tblcomputers.computername -> tblassets.assetid
Tblcompcustom -> tblassetcustom
Tblcustdevices -> tblassets
Tblcustdevices.devicekey -> tblassets.assetunique
All other tables: computername -> assetid

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
TsysChassisTypes.ChassisName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSname,
tblAssets.SP,
tblOperatingsystem.InstallDate,
tblOperatingsystem.SerialNumber,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Serialnumber As AssetTag,
tblAssets.Memory,
tblAssets.Processor,
tblNetwork.Description As NIC,
tblNetwork.IPAddress,
tblNetwork.MACaddress,
tblDiskdrives.Caption As [Disk],
Ceiling(tblDiskdrives.Freespace / 1024 / 1024) As FreeMB,
Ceiling(tblDiskdrives.Size / 1024 / 1024) As TotalMB,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where Ceiling(tblDiskdrives.Size / 1024 / 1024) <> 0 And tblAssetCustom.State =
1 And tblNetwork.IPEnabled = 1 And tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName,
[Disk]

chaley wrote:
I would also like to add installed drives such as CD's and Floppy's if possible.

You cannot include all of this information in a single report, as this will lead to duplication. You will need to build a separate report based on tblCDROMDrive/tblFloppy.