Physical Laptops and Desktops Only

[Tke402]

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

[pryan67]

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

[lunesolitaire]

Thank you Pryan67,  this works fine for me to get a whole liste of Desktops/Laptops,  

by the way,  if I want to add a "LsAgent Client" (yes/no)  colonne in the output,  what to do?

[Tke402]

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

[mwrobo09]

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.