Custom report based on IP Location report

[Jono]

Hello - I have several IP Locations defined. On one in particular called "SEND-WS System" I want to add the Location column to the report. I know that this report is not editable, but can I create a custom report that gives me exactly the same thing as the built-in report, with the Location column included?

Thanks,
Jono

[Jono · Accepted solution]

Yes, Salamine, that's exactly what I needed. Thanks a ton!

The main thing I needed was to take the default report and add the asset Location to it so that we could pull up the report and see the location of each device without having to click into each one. So, I took what you gave me and added to it to look like this (in case it might help anyone else):

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Location,
  tsysAssetTypes.AssetTypename As Type,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.IPAddress As [IP Address],
  tblAssets.Lastseen
From tblAssets
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysIPLocations.IPLocation = 'SEND-WS System'
Order By tblAssetCustom.OrderNumber

I also added an order number to each device (1-9,A,B) and ordered by that hidden column so we can see the devices in order down the line.

Thanks again!
Jono

[Salamine]

Edit:

You probably just want code.

Select Top 1000000 tsysIPLocations.IPLocation,
  tblAssets.AssetID,
  tblAssets.AssetName
From tblAssets
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Order By tsysIPLocations.IPLocation,
  tblAssets.AssetName

Add in the other fields you want.

-Sal

[Jono]

Unlike a lot of the reports, there's no "Edit Report" link that will show me the SQL code for that report. I understand that's not the type of report that LS wants us to edit (it's auto-generated), but in order to do what I want to do, I need the code for that report.

[stimpy_555]

If it is defined in the IP Subnet information of Lansweeper cant you get the detail required from the following report?

http://YOURSERVER:PORT/report.aspx?det=web40repIPLocationlist&title=IP+Location%3a+IP+Address+list

This is by;

AssetName, Domain, Type, IPAddress, IPLocation, LastSeen

Regards

[stimpy_555]

Where are your IP Locations defined? in AD Sites and Services or are they defined under the IP Range Location within LanSweeper? Can you please identify the report you wish to modify?

[Jono]

Hi stimpy_555. Thanks for the reply.

stimpy_555 wrote:
Where are your IP Locations defined? in AD Sites and Services or are they defined under the IP Range Location within LanSweeper?

They are defined in the IP Range Location in LS.

stimpy_555 wrote:
Can you please identify the report you wish to modify?

It's the Assets in IP Location SEND-WS System report. I get it by going to the Assets menu and choosing it under IP Locations.

Thanks again!
Jono