→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

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

View solution in original post

6 REPLIES 6
Jono
Champion Sweeper II
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
Engaged Sweeper III
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
Champion Sweeper II
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
Engaged Sweeper III
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
Engaged Sweeper III
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
Champion Sweeper II
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