LANSweeper Report excluding Assets with IP address for the asset name

[justanotherbod]

Hi

Im fairly new to LANSweeper and creating reports and im trying to create a report for only LINUX machines EXCLUDING any ASSETNAMES that have an IP Address for a name.

So far i have this, but i cant exclude the IP address's from the name.

Im sure its something probably VERY basic, but i cant quite seem to find it.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease) As OS,
tblAssets.Memory,
tblAssets.Processor,
tblAssetUserRelations.Username As Owner,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Model,
tblAssetCustom.SSHServer
From tblComputersystem
Right Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Where (tblAssets.AssetID Not Like '192.168.%' And tsysAssetTypes.AssetTypename
Not Like 'windows' And tblComputersystem.Domainrole > 1 And
tblAssetCustom.State = 1) Or
(tsysAssetTypes.AssetTypename = 'Linux' And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

Any assistance greatly appreciated.

Many thanks for your help.

[justanotherbod]

Thanks for the speedy response Kevin.

I made the change and it didnt work. But on a whim/as a test, i moved the "not like '192.168.%'" to the OR column in the report creator and it works. Its like theres something wrong with the Criteria column. 

This is the code now.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease) As OS,
tblAssets.Memory,
tblAssets.Processor,
tblAssetUserRelations.Username As Owner,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Model,
tblAssetCustom.SSHServer
From tblComputersystem
Right Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Where (tsysAssetTypes.AssetTypename Not Like 'windows' And
tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1) Or
(tblAssets.AssetName Not Like '192.168.%' And tsysAssetTypes.AssetTypename =
'Linux' And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

Iv also attached a screenshot. 

Like I say, im a bit of a novice with reports and these syntax's, but iv managed to cobble a few other reports together before, but this 1 seems bugged. 

Im running LS version 11.1.8.0. Appreciate any advice. 

[KevinA-REJIS]

Try editing this line (change in bold)

Where (tblAssets.AssetName Not Like '192.168.%' And tsysAssetTypes.AssetTypename

[justanotherbod]

Ok. So my Boss has completely changed the criteria of the report now and wants all assets on our backend infrastructure network, so I have completely rejigged the  report and it looks MUCH more like what he wants.

HOWEVER, its seems a few servers in 1 location are duplicated. If I search for these assets under the ASSETS>DOMIAN and then our domain, and search for the AssetName, I can see only 1.

However, when I run the below report, I get duplicate servers for any ASSETNAMES starting NYC. 

Scan times, hostnames, usernames, IP address, etc all seem to be the same!

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.State As State,
tblState.Statename,
tblAssetCustom.BarCode,
tblAssetUserRelations.Username As Owner,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysIPLocations.IPLocation,
tsysAssetTypes.AssetTypename,
tblOperatingsystem.Caption
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblState.Statename = 'active' And tsysIPLocations.IPLocation Like 'uc%')
Or
(tsysIPLocations.IPLocation Like '%server%') Or
(tsysIPLocations.IPLocation Like 'chassis%') Or
(tsysIPLocations.IPLocation Like 'cucm%')
Order By tblAssets.AssetName

Would really appreciate any help on cracking this.

[KevinA-REJIS · Accepted solution]

See if adding Distinct to the first line makes a difference:

Select Distinct Top 1000000 tblAssets.AssetID

[justanotherbod]

ABSOLUTE LEGEND.

Thanks Kev