Multi query failing/crashing program

[cpmining]

Hi

First of all I would like to thank the developers of this program as it's an asset to my department. We are still in the review stages and depending on its performance we will look at purchasing an Enterprise licence.

That being said, I am having difficulty creating a query that can capture the following for me

Username | Computer Name | Computer Serial | IP Address | IP Location | Monitor Serial | Monitor Make | Monitor Manufacture Date

At this point in time I have to run two queries to obtain the information I need and it's not ideal. Yes I get the info but I have to try and combine two spreadsheets which is time consuming.

If I could somehow manage to get the report to provide all those details, the software would be a definite purchase for us and would assist grealy in our yearly hardware audits.

Looking forward to your suggestions.

[cpmining]

OK slowly starting to figure this out

so far I have this:

Select Top 1000000 tblAssets.Username,
tblAssets.AssetUnique,
tblAssets.AssetID,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As Serial,
tblAssets.IPAddress As [IP Address],
tblAssetCustom.Warrantydate,
tblMonitor.MonitorModel,
tblMonitor.SerialNumber
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblMonitor On tblAssets.AssetID = tblMonitor.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetID

Is there any way for the MONITOR fields to display side by side rather than having two results per user?

[Hemoco]

cpmining wrote:
Is there any way for the MONITOR fields to display side by side rather than having two results per user?

No, as SQL displays values in a field as lines, not columns.

[cpmining]

Hi support team.

With Lansweeper 5 this report no longer works.

Can someone please assist? When I try and create the custom report I get the error message:

The specified table does not exist. [ tblComputers ]

[Hemoco · Accepted solution]

cpmining wrote:
With Lansweeper 5 this report no longer works.

There is a built-in report "Computer: System configuration overview" you can start from. It can be found under Dashboard/Reports/All Reports.

[cpmining]

Lansweeper wrote:

cpmining wrote:
With Lansweeper 5 this report no longer works.

There is a built-in report "Computer: System configuration overview" you can start from. It can be found under Dashboard/Reports/All Reports.

I'm not familiar with V5 Report Builder as there is no "preview" button like in V4.

Can I ask someone just to modify the V4 report from my post to suit V5?

Basically all I want is a report that returns the following:

AD Username | Computer Name | | Computer Serial | Ip Address | Location | Warranty Expiry | Monitor 1 SN | Monitor 1 Model | Monitor 2 SN | Monitor 2 Model

[Hemoco]

Please use the report below for the information you are after.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, tblComputers.LastknownIP As IP, tblComputerSystemProduct.IdentifyingNumber As Tag, web40AllIPLocations.IPLocation, tblMonitor.MonitorModel, tblMonitor.MonitorManufacturer, tblMonitor.SerialNumber As [Monitor Serial], tblMonitor.ManufacturedDate As [Monitor Manufactured], tblBIOS.Caption As BIOS, tblBIOS.ReleaseDate As [BIOS Release], DateDiff(yy, tblBIOS.ReleaseDate, GetDate()) As [Computer Age], tblComputers.Username As [Last User], tblComputers.Userdomain As [Last User Domain] From tblComputers Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join web40AllIPLocations On web40AllIPLocations.Computername = tblComputers.Computername Left Join tblMonitor On tblMonitor.Computername = tblComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Where tblComputers.Lastseen <> '' Order By tblComputers.ComputerUnique

[cpmining]

Actually I have half found my answer from another post and I like the fact it shows the age of the computer

Is there a way I can combine the code from above with the following?

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblBIOS.Caption, tblBIOS.ReleaseDate, tblADusers.Name,
tblADusers.Company, DateDiff(yy, tblBIOS.ReleaseDate, GetDate()) As Age
From tblComputers Inner Join
tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join
tblADusers On tblADusers.Username = tblComputers.Username And
tblADusers.Userdomain = tblComputers.Userdomain

[cpmining]

Perfect! Thank you so much. Appreciate the quick reply.

There is only 1 more field I forgot to mention and I'm not sure if it's available.

Is there a way to obtain the COMPUTER Manufacture Date at all?

Thanks in advance

[Hemoco]

Please try this sql query

Select top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblComputers.Userdomain, tblComputers.Username,
tblComputers.LastknownIP, tblComputerSystemProduct.IdentifyingNumber,
web40AllIPLocations.IPLocation, tblMonitor.SerialNumber,
tblMonitor.MonitorModel, tblMonitor.MonitorManufacturer,
tblMonitor.ManufacturedDate
From tblComputers Inner Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Inner Join
web40AllIPLocations On web40AllIPLocations.Computername =
tblComputers.Computername Left Join
tblMonitor On tblMonitor.Computername = tblComputers.Computername
Order By tblComputers.ComputerUnique