Notification

Icon
Error

Report on Assets in a Static Group

Posted: Monday, May 11, 2020 5:25:40 PM(UTC)
Chris H

Chris H

Member Original PosterPosts: 13
0
Like
I do not speak SQL. I need some help with what should be a pretty simple report to create.

I need to take the built in report "All licensed assets in Lansweeper" but filter it to a static asset group I created called Network Devices.

NOTE: This static group is not to be confused with the Network Device asset type. This is static group I created which contains a collection of devices such as routers, switches, firewalls, etc...



Andy.S
#1Andy.S Member Posts: 27  
posted: 5/22/2020 2:55:03 PM(UTC)
Hi , Is this what your after :

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Domain,
  Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
  As OS,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Mac As MACAddress,
  tblADComputers.OU,
  tblState.Statename As State,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Description,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssets.FQDN,
  tblAssetCustom.DNSName,
  tblAssetCustom.LastPatched,
  tblAssetCustom.LastFullbackup,
  tblAssetCustom.LastFullimage,
  tblAssetCustom.Location,
  tblAssetCustom.Building,
  tblAssetCustom.Department,
  tblAssetCustom.Branchoffice,
  tblAssetCustom.BarCode,
  tblAssetCustom.Contact,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.OrderNumber,
  tblAssetCustom.Custom1,
  tblAssetCustom.Custom2,
  tblAssetCustom.Custom3,
  tblAssetCustom.Custom4,
  tblAssetCustom.Custom5,
  tblAssetCustom.Custom6,
  tblAssetCustom.Custom7,
  tblAssetCustom.Custom8,
  tblAssetCustom.Custom9,
  tblAssetCustom.Custom10,
  tblAssetCustom.Custom11,
  tblAssetCustom.Custom12,
  tblAssetCustom.Custom13,
  tblAssetCustom.Custom14,
  tblAssetCustom.Custom15,
  tblAssetCustom.Custom16,
  tblAssetCustom.Custom17,
  tblAssetCustom.Custom18,
  tblAssetCustom.Custom19,
  tblAssetCustom.Custom20,
  Case tblAssetCustom.PreventCleanup
    When 0 Then 'No'
    When 1 Then 'Yes'
  End As PreventCleanup,
  tblAssets.Scanserver,
  tblAssetGroups.AssetGroup
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
Where tblAssetGroups.AssetGroup Like '%Network Devices%' And tblAssets.Assettype <> 208
Order By tblAssets.AssetName


Chris H
#2Chris H Member Original PosterPosts: 13  
posted: 6/10/2020 8:26:51 PM(UTC)
I opened a case with support and they provided me we with this:

Quote:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Domain,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
As OS,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetGroups.AssetGroup,
tblAssets.Mac As MACAddress,
tblADComputers.OU,
tblState.Statename As State,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Description,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.LastPatched,
tblAssetCustom.LastFullbackup,
tblAssetCustom.LastFullimage,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom1,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssetCustom.Custom4,
tblAssetCustom.Custom5,
tblAssetCustom.Custom6,
tblAssetCustom.Custom7,
tblAssetCustom.Custom8,
tblAssetCustom.Custom9,
tblAssetCustom.Custom10,
tblAssetCustom.Custom11,
tblAssetCustom.Custom12,
tblAssetCustom.Custom13,
tblAssetCustom.Custom14,
tblAssetCustom.Custom15,
tblAssetCustom.Custom16,
tblAssetCustom.Custom17,
tblAssetCustom.Custom18,
tblAssetCustom.Custom19,
tblAssetCustom.Custom20,
Case tblAssetCustom.PreventCleanup
When 0 Then 'No'
When 1 Then 'Yes'
End As PreventCleanup,
tblAssets.Scanserver
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tblAssetGroups.AssetGroup = 'Network Devices' And tblAssets.Assettype <> 208
Order By tblAssets.AssetName

Active Discussions

Lansweeper Help Desk Workflow
by  lswanson   Go to last post Go to first unread
Last post: Yesterday at 11:12:21 PM(UTC)
Lansweeper Changing to remote scanning due to COVID
by  FrankSc  
Go to last post Go to first unread
Last post: Yesterday at 9:35:26 PM(UTC)
Lansweeper Not giving hackers the Domain Admin password / account
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 9:27:13 PM(UTC)
Lansweeper LsAgent failing - Lansweeper SSL Expired
by  lansweeper25t34  
Go to last post Go to first unread
Last post: Yesterday at 8:33:28 PM(UTC)
Lansweeper Is there a chance to get the firewall off via Lansweeper?
by  EDV_OHZ   Go to last post Go to first unread
Last post: Yesterday at 4:57:26 PM(UTC)
Lansweeper Merge Asset button
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/5/2020 9:27:54 PM(UTC)
Lansweeper Merge Two Assets or Update Based on Serial Number
by  KeithBecker   Go to last post Go to first unread
Last post: 8/5/2020 9:25:16 PM(UTC)
Lansweeper Hyper-V guest assets
by  bgstein  
Go to last post Go to first unread
Last post: 8/5/2020 5:50:24 PM(UTC)