Notification

Icon
Error

Physical Laptops and Desktops Only

Posted: Monday, June 1, 2020 12:55:20 PM(UTC)
Tke402

Tke402

Member Original PosterPosts: 2
0
Like
Hi, I'm in need of a report showing ONLY physical laptops and desktops regardless of the OS (we have Linux, Mac, and Windows). I can get bits and pieces of this information from other reports but I have no luck making a single report that covers it all. I would like the same fields shown in the default report "Assets: All columns from the Assets menu"

It would be a great feature in LanSweeper to be able to filter a list then export that as a report query. For example, I can take the default report "Assets: All columns from the Assets menu" filter it for assets that are in "stock" state, for example, then export this to a query where I can make a report from.

Anyway, here are my criteria for this report.

1. All PHYSICAL assets that are in "active" state.
2. Are either a laptop, tablet or desktop
3. Does not matter what OS the machine is running (we have Linux, Mac, and Windows)
4. The report can have all the same fields as the canned report: "Assets: All columns from the Assets menu"

I assume the best approach to this would be to modify the canned report "Assets: All columns from the Assets menu" for the items i need? Can anyone help with that?

Thanks,
TKE402
mwrobo09
#1mwrobo09 Member Posts: 54  
posted: 6/3/2020 3:52:16 PM(UTC)
If that report has all the fields you need, I would click edit the report and then copy the SQL code. Then hit the back button and get out of editor and then create a new report and paste the SQL code at the bottom. Edit the criteria on the fields and save under a different name.
Tke402
#2Tke402 Member Original PosterPosts: 2  
posted: 6/3/2020 7:12:54 PM(UTC)
Thanks I did that but I guess I should rephrase the question. How do I edit the query to only show machines that are:

1. Only PHYSICAL assets that are in "active" state.
2. Are either a laptop, tablet or desktop NO server hardware
3. Does not matter what OS the machine is running (we have Linux, Mac, and Windows)


The out of the box query shows VMWare Guests, Servers, Monitors etc.

Here's the query below:

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
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
Where tblAssets.Assettype <> 66
Order By tblAssets.AssetName
pryan67
#3pryan67 Member Posts: 80  
posted: 6/5/2020 1:51:56 PM(UTC)
Try this:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblPortableBattery.AssetID Is Null Then 'Desktop'
Else 'Laptop'
End As [Desktop/Laptop]
From tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
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 tblState.Statename = 'Active' And
tsysAssetTypes.AssetTypename In ('Windows', 'Windows CE', 'apple mac',
'linux')
Order By tblAssets.Domain,
tblAssets.AssetName

Active Discussions

Lansweeper Lost Configuration tab (Admin rights)
by  kspap   Go to last post Go to first unread
Last post: Today at 10:30:12 AM(UTC)
Lansweeper Deployment with different user rights
by  Jupiter_IT  
Go to last post Go to first unread
Last post: Today at 9:39:20 AM(UTC)
Lansweeper cisco fuji device not linking with connected devices
by  char   Go to last post Go to first unread
Last post: Yesterday at 9:12:07 AM(UTC)
Lansweeper Microsoft CVE-2020-1425
by  Richard_B  
Go to last post Go to first unread
Last post: 7/3/2020 4:29:41 PM(UTC)
Lansweeper Wake on Lan in VLANs
by  FrankSc   Go to last post Go to first unread
Last post: 7/3/2020 1:56:46 PM(UTC)
Lansweeper Ticket Closed = email to all helpdesk desk agents
by  Laurent Maene  
Go to last post Go to first unread
Last post: 7/3/2020 1:23:49 PM(UTC)
Lansweeper Helpdek Call Re-Opened
by  pryan67   Go to last post Go to first unread
Last post: 7/3/2020 1:12:17 PM(UTC)
Lansweeper Office 365 32bit vs 64bit?
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/2/2020 10:35:19 PM(UTC)