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: 94  
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: 434  
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 Unable to send to External Email
by  pryan67   Go to last post Go to first unread
Last post: Yesterday at 8:21:01 PM(UTC)
Lansweeper Lansweeper assets not being Scanned
by  Jordan  
Go to last post Go to first unread
Last post: Yesterday at 6:42:22 PM(UTC)
Lansweeper Database size growing too large
by  bladd   Go to last post Go to first unread
Last post: Yesterday at 4:26:44 PM(UTC)
Lansweeper Searching Specific File
by  mzipperer  
Go to last post Go to first unread
Last post: Yesterday at 4:23:29 PM(UTC)
Lansweeper Exchange 2010 information is not populating
by  Moe   Go to last post Go to first unread
Last post: Yesterday at 12:30:38 PM(UTC)
Lansweeper Routinely Exploited Vulnerabilities Query Report
by  pryan67  
Go to last post Go to first unread
Last post: Yesterday at 12:25:31 PM(UTC)
Lansweeper Creating a report for new devices every 2 hours to alert
by  Moe   Go to last post Go to first unread
Last post: 6/4/2020 7:26:11 PM(UTC)
Lansweeper Changes in the licensing model
by  BullGates  
Go to last post Go to first unread
Last post: 6/4/2020 6:27:23 PM(UTC)