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

Installer Installer - Microsoft Office
by  Florian_Eigsi   Go to last post Go to first unread
Last post: 6/29/2020 3:33:20 PM(UTC)
Installer Firefox ESR 68.8.0 MSI Installer
by  PLSJohnJohn   Go to last post Go to first unread
Last post: 5/11/2020 4:33:50 PM(UTC)
Installer Update Chrome browser with GoogleUpdate.exe ?
by  Inna Ptushkina  
Go to last post Go to first unread
Last post: 5/4/2020 8:39:29 PM(UTC)
Installer Bios Update for Dell all in one
by  Florian_Eigsi   Go to last post Go to first unread
Last post: 4/2/2020 11:36:43 AM(UTC)
Installer Windows 10 Upgrade
by  Michael Kop  
Go to last post Go to first unread
Last post: 3/27/2020 4:47:52 PM(UTC)
Installer Uninstall - Adobe Acrobat 9x
by  palemmo   Go to last post Go to first unread
Last post: 3/25/2020 7:25:05 PM(UTC)
Installer OneDrive Silent Install
by  Steven.C  
Go to last post Go to first unread
Last post: 3/13/2020 2:55:19 PM(UTC)