Report Builder - All devices by IP Location

[firmdale]

Hi

Can someone help me to build report to show following:

-device make
-device model
-device serial number
-device IP address
-device MAC address

Only show devices from specific IPLocation

I tried report below but this only shows 25 devices out of 73 from that CGH LAN subnet 😞

Select Top 1000000 tblAssets.IPAddress As IP,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Mac,
tsysOS.OSname,
tsysOS.Image
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.IPAddress <> '' And tsysIPLocations.IPLocation = 'CGH LAN'
Order By tblAssets.IPNumeric


What am I doing wrong here?

Thanks in advance.

[RCorbeil]

The INNER JOIN against tSysOS is dropping any devices that aren't computers. Change that to a LEFT JOIN.

INNER JOIN will filter out the row if the table you're joining to has no matching records. LEFT JOIN will return the row from the primary table (tblAssets) regardless of whether there are any corresponding rows in the secondary table (tSysOS).

...

Ah, I see you already discovered that.

[firmdale · Accepted solution]

Sorry I forgot to mention that report shows computers but no other devices.
I want full asset list: switches, firewall, all IP devices.

Contacted support and got really quick reply and solution, see below:


Hi,

Other devices do not have an Oscode, so you will need to change the link to have them all.

Would suggest the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress As IP,
tblAssets.Mac,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tsysOS.OSname,
tsysAssetTypes.AssetTypeIcon16 As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.IPAddress <> '' And tsysIPLocations.IPLocation = 'CGH LAN'
Order By tblAssets.IPNumeric


Regards,

The Lansweeper team

[RCorbeil]

In theory it should work. I ran a version, modified as below, against my data and got back what was expected.

As a test, add tsysIPLocations.IPLocation to the list of SELECTed fields and remove tsysIPLocations.IPLocation = 'CGH LAN' from the WHERE clause to verify that the correct values are being selected.