Windows Assets report - Active and Non Active

[Martyn_Umpleby]

Hello,

Could anyone help how I can get a full list of all active and ALL non active assets, at the moment i'm unable to show all inactive Microsoft hardware or assets contained in the domain OU (not listed here).


Type | Vendor | Asset Type | Model | Assetname | Service Tag Number | AssetTag | Allocated ID | Displayname | Asset state | PO Number | PurchaseDate | Warranty End Date | Operatingsystem | Domain | Lastseen


Select Top 1000000 Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop' End End As Type,
tblAssetCustom.Manufacturer As Vendor,
tsysAssetTypes1.AssetTypename As [Asset Type],
tblAssetCustom.Model,
tblAssets.AssetUnique,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblAssetCustom.Custom5 As AssetTag,
tblAssetCustom.Contact As [Allocated ID],
tblADusers.Displayname,
tblState.Statename As [Asset state],
tblAssetCustom.Custom3 As [PO Number],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate As [Warranty End Date],
tblAssetCustom.Custom2 As [Invoice number],
tblOperatingsystem.Caption As Operatingsystem,
tblAssets.Domain,
tblAssetCustom.AssetID,
tblAssets.Lastseen
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Where tsysAssetTypes1.AssetTypename Not Like '%Webserver%' And
tsysAssetTypes1.AssetTypename Not Like '%Network device%' And
tsysAssetTypes1.AssetTypename Not Like '%NAS%' And
tsysAssetTypes1.AssetTypename Not Like '%Vmware server%' And
tsysAssetTypes1.AssetTypename Not Like '%Remote Access Controller%' And
tsysAssetTypes1.AssetTypename Not Like '%Printer%' And
tsysAssetTypes1.AssetTypename Not Like '%Switch%' And
tsysAssetTypes1.AssetTypename Not Like '%Linux%' And
tsysAssetTypes1.AssetTypename Not Like '%VOIP phone' And
tblAssetCustom.Model Not Like '%Vmware%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

Thanks in advance.

[Tom_P]

As stated by ahedden, to include all inactive assets you need to change the following filter

tblAssetCustom.State = 1

to

(tblAssetCustom.State = 1 or tblAssetCustom.State = 2)

or to

tblAssetCustom.State in (1, 2)

A list of the available (custom) states defined in Lansweeper can be obtained by running the following SQL:

select ticketstateid, statename, description from htblticketstates

To include all assets you can include all ticketstateid's or remove the filter entirely.

[ahedden]

you should be able to change tblAssetCustom.State = 1 to (tblAssetCustom.State = 1 or tblAssetCustom.State = 2)