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

Hi all thanks for taking the time to read this 

I am trying to create a lansweeper report that only shows all desktops and laptops including some other fields which works but for some reason its also showing our VMWARE systems which I need to filter out please could anyone assist, I have crawled through the forums and have started to go crazy now 

My report runs like this, I am unsure on how or where to add the excluded manufacturer VMware, Inc.

 

DaveWard_0-1666607961966.png

 

1 ACCEPTED SOLUTION
DaveWard
Engaged Sweeper II

Hi Yes i did some searching around other topics and just did some copying and pasting until it worked hahaha bits and bats of other peoples reports but this gives me exactly what I need now so thanks to everyone 

Select Top 1000000 tblAssets.AssetID,

  tblAssets.AssetName,

  tblAssets.Domain,

  tblAssets.Username,

  tblADusers.email As Email,

  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,

  tblAssetCustom.Manufacturer As Make,

  tblAssetCustom.Model As Model,

  tsysOS.OSname As OS,

  tblAssets.Lastseen,

  tblAssetCustom.Warrantydate As [Warranty Expiration],

  tblState.Statename As State,

  Case

    When tblPortableBattery.AssetID Is Null Then 'Desktop'

    Else 'Laptop'

  End As [Desktop/Laptop]

From tblAssets

  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode

  Left Join tblADusers On tblADusers.Username = tblAssets.Username

  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype

  Inner Join tsysIPLocations On tsysIPLocations.LocationID =

      tblAssets.LocationID

  Inner Join tblState On tblState.State = tblAssetCustom.State

  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID

Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or

    tblAssetCustom.Model Not Like '%Virtual%') And

  tsysOS.OSname Not Like '%win 20%%' And tblAssets.Lastseen Is Not Null And

  tblAssets.Lastseen <> '' And tsysAssetTypes.AssetTypename In ('Windows',

  'Windows CE', 'apple mac', 'linux')

Order By tblAssets.Domain,

  tblAssets.AssetName

View solution in original post

5 REPLIES 5
rom
Champion Sweeper III

I usually just put "tblassetcustom.manufacturer not like '%VMware%'"  in the WHERE clause, to filter out vmware stuff, and I think you can do %Microsoft%' as well, at least for windows computers, to rule out virtual stuff for Hyper-V.

beacampos
Lansweeper Employee
Lansweeper Employee

Hello, @DaveWard , glad to hear you have been able to solve the problem. Would you mind sharing with our Community what was the solution? This will be valuable for others joining that face the same situation! thank you !

DaveWard
Engaged Sweeper II

Hi Yes i did some searching around other topics and just did some copying and pasting until it worked hahaha bits and bats of other peoples reports but this gives me exactly what I need now so thanks to everyone 

Select Top 1000000 tblAssets.AssetID,

  tblAssets.AssetName,

  tblAssets.Domain,

  tblAssets.Username,

  tblADusers.email As Email,

  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,

  tblAssetCustom.Manufacturer As Make,

  tblAssetCustom.Model As Model,

  tsysOS.OSname As OS,

  tblAssets.Lastseen,

  tblAssetCustom.Warrantydate As [Warranty Expiration],

  tblState.Statename As State,

  Case

    When tblPortableBattery.AssetID Is Null Then 'Desktop'

    Else 'Laptop'

  End As [Desktop/Laptop]

From tblAssets

  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode

  Left Join tblADusers On tblADusers.Username = tblAssets.Username

  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype

  Inner Join tsysIPLocations On tsysIPLocations.LocationID =

      tblAssets.LocationID

  Inner Join tblState On tblState.State = tblAssetCustom.State

  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID

Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or

    tblAssetCustom.Model Not Like '%Virtual%') And

  tsysOS.OSname Not Like '%win 20%%' And tblAssets.Lastseen Is Not Null And

  tblAssets.Lastseen <> '' And tsysAssetTypes.AssetTypename In ('Windows',

  'Windows CE', 'apple mac', 'linux')

Order By tblAssets.Domain,

  tblAssets.AssetName

beacampos
Lansweeper Employee
Lansweeper Employee

Hello @DaveWard that's great, thanks a lot for sharing šŸ™‚ 

DaveWard
Engaged Sweeper II

This can be closed now thank

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now