Notification

Icon
Error

Lansweeper report to match computer name - Title

Posted: Tuesday, February 18, 2020 4:48:51 PM(UTC)
roro

roro

Member Original PosterPosts: 2
1
Like
This issue has been solved! Click here to view the solution
Here is my SQL
Quote:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblOperatingsystem.Caption As FullOSname,
tblAssets.Version As [Windows Version]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblAssets.AssetName Like 'LT-FR' And tsysOS.OSname = 'win 10' And
tblAssetCustom.State = 1) Or
(tblAssets.AssetName Like 'PC-FR')


I'm trying to match only computers that start with "LT-FR" or "PC-FR".
Erik.T
#1Erik.T Member Administration Posts: 63  
posted: 2/19/2020 11:41:39 AM(UTC)
Hi roro,

I moved your post to the report questions as you will be more likely to get an answer there.
roro
#2roro Member Original PosterPosts: 2  
posted: 2/19/2020 2:42:32 PM(UTC)
Originally Posted by: Erik.T Go to Quoted Post
Hi roro,

I moved your post to the report questions as you will be more likely to get an answer there.


I actually solved this issue myself.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblOperatingsystem.Caption As FullOSname,
tblAssets.Version As [Windows Version]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblAssets.AssetName Like '%LT-FR%' And tsysOS.OSname = 'win 10'
And tblAssetCustom.State = 1) Or
(tblAssets.AssetName Like '%PC-FR%')
RC62N
#3RC62N Member Posts: 430  
posted: 2/19/2020 5:02:27 PM(UTC)
You may want to confirm your WHERE clause.
Code:
Where
  ( tblAssets.AssetName Like '%LT-FR%'
    And tsysOS.OSname = 'win 10'
    And tblAssetCustom.State = 1)
  Or
  (tblAssets.AssetName Like '%PC-FR%')

You're saying
Code:
  (asset name contains "LT-FR"
    AND OS is Win10
    AND asset state is active
  )
  OR
  (asset name contains "PC-FR"
    AND don't care what OS
    AND any state (active, non-active, sold, broken, etc.)
  )

If you mean that you want active Win10 machines that meet the two name conditions, you'll want to change that to
Code:
Where
  tblAssetCustom.State = 1
  And tsysOS.OSname = 'win 10'
  And ( tblAssets.AssetName Like '%LT-FR%'
        Or tblAssets.AssetName Like '%PC-FR%')

Active Discussions

Lansweeper Find machines missing any 1 of several apps
by  BennettL   Go to last post Go to first unread
Last post: Yesterday at 8:35:28 PM(UTC)
Lansweeper Max Software Version and Deploy to Only Outdated
by  GenDev   Go to last post Go to first unread
Last post: Yesterday at 1:06:50 PM(UTC)
Lansweeper Patch Tuesday - Superseded MS Updates
by  doone128  
Go to last post Go to first unread
Last post: Yesterday at 11:27:10 AM(UTC)
Lansweeper Report on basic Router information
by  Liljack  
Go to last post Go to first unread
Last post: 3/30/2020 7:44:22 AM(UTC)
Lansweeper Windows: Static IP address configured (Built-in)
by  RobinPSU   Go to last post Go to first unread
Last post: 3/27/2020 8:52:12 PM(UTC)
Lansweeper Adding top user to asset report
by  Argon0  
Go to last post Go to first unread
Last post: 3/26/2020 5:20:51 PM(UTC)