[Solve this Winter Challenge ]Help identify workstations which have not been scanned...

[Mercedes_O]

Happy New Year All! I hope January is treating you well so far. Thank you for your patience over the holiday period - we look forward to providing you with more support and peer to peer discussions as the year progresses.

Here is a fresh challenge to tackle,reply to the scenario below and you will receive a special Winter Challenge badge from our team!

Winter Challenge Scenario:

Sometimes you may not be interested in servers in your reports (however servers may be included in Lansweeper reports as staff log into servers).How can you exclude servers from your reports and just report on workstations? For example, how would you report on workstations that are already detected by Lansweeper but have yet to be successfully scanned?

[Mercedes_O]

This challenge is now closed! Thank you everyone who participated in our Winter Challenge, you have unlocked an exclusive Sweepy Hero badge for your community profile - Bat Sweepy!

Stay tuned we will announce our spring challenge very soon 🙂

sweepy badgeSweepy batman

[rom]

Tsk Tsk -  you're thinking about it all wrong.  Wipe and Sell all of your servers, and delete the LS assets.  Boom, no more servers on reports, you make money by selling them, you free up LS licenses, and you decrease your carbon footprint.

[LSEngineer2007]

I suppose that is one of way of doing it.  Also, why it's so important to hear all ideas.  We can make some cash with this one!!

[LSEngineer2007]

Select Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypename As [Asset Type],
tblAssetCustom.Serialnumber As [Serial #],
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Username As [Last Login],
tblState.Statename,
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tblAssets.Lastseen As [Last Seen],
tblAssets.Lasttried As [Last Tried]
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblDomainroles.Domainrolename In ('Stand-alone workstation',
'Member workstation')

[Mister_Nobody]

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Mac,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lasttried,
  tblAssets1.AssetID As deviceassetid,
  tblAssets1.AssetName As deviceassetname,
  tblSNMPAssetMac.IfIndex As SwitchPort,
  tblSNMPInfo.IfDescription As PortDescription,
  tblSNMPInfo.IfSpeed / 1000000 As ifSpeedMb,
  tblAssets.Lastseen,
  tblAssets.LastseenCredentials,
  tblAssets.LasttriedCredentials,
  tblAssets.LasttriggeredCredentials
From tblAssets
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Left Join tblSNMPAssetMac On tblAssets.Mac = tblSNMPAssetMac.AssetMacAddress
  Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblSNMPAssetMac.AssetID
  Left Join tblSNMPInfo On tblAssets1.AssetID = tblSNMPInfo.AssetID And
      tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Where tblAssets.Assettype = -1 And IsNull(tblAssets.oscode, '') = ''
Order By tblAssets1.lastseen

[Hendrik_VE]

Made it color-coded to see which ones are online (if relevant switch information is scanned):

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Mac,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lasttried,
  tblAssets.Lastseen,
  tblAssets1.AssetID As deviceassetid,
  tblAssets1.AssetName As deviceassetname,
  tblSNMPInfo.IfDescription As PortDescription,
  tblSNMPInfo.IfSpeed / 1000000 As ifSpeedMb,
  Case
    When tblSNMPInfo.IfOperstatus = 1 And tblSNMPInfo.IfAdminstatus = 1 Then
      '#d4f4be'
    When tblSNMPInfo.IfOperstatus = 2 And tblSNMPInfo.IfAdminstatus = 1 Then
      '#ffff00'
    When tblSNMPInfo.IfOperstatus = 2 And tblSNMPInfo.IfAdminstatus = 2 Then
      '#ffadad'
    Else '#00ffff'
  End As backgroundcolor
From tblAssets
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Left Join tblSNMPAssetMac On tblAssets.Mac = tblSNMPAssetMac.AssetMacAddress
  Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblSNMPAssetMac.AssetID
  Left Join tblSNMPInfo On tblAssets1.AssetID = tblSNMPInfo.AssetID And
    tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Where tblAssets.Assettype = -1 And IsNull(tblAssets.OScode, '') = ''
Order By tblAssets1.Lastseen

[Hendrik_VE]

Happy new year to you too Mercedes 🙂
It's a challenge to understand the challenge 😅
Could you repeat the question?

[Mercedes_O]

Happy New Year Hendrik! Haha so sorry I tried my best - the question is how would you report on workstations that are already detected by Lansweeper but have yet to be successfully scanned? (also edited the original post to make it clearer)