→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
justanotherbod
Engaged Sweeper II

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.

1 ACCEPTED SOLUTION

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

Select Distinct Top 1000000 tblAssets.AssetID

View solution in original post

5 REPLIES 5
justanotherbod
Engaged Sweeper II

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
Champion Sweeper III

Try editing this line (change in bold)

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

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.

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

Select Distinct Top 1000000 tblAssets.AssetID

ABSOLUTE LEGEND.

Thanks Kev