Report for end of warranty

[tamirt]

HI

I need a reports for:
1. all desktop pc that warranty end until 31.12.2017
2. all laptop that warranty end until 31.12.2017
3. all users that have more then one laptop/desktop and to see what model of laptop/ desktop they have.

Yours
Tamir

[Niko0 · Accepted solution]

This report will show how many computers the user is currently logged into, This will be the best way to determine if the user has more than 1 pc.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Model,
  Case When (TsysChassisTypes.ChassisName Like '%Docking Station%' Or
    TsysChassisTypes.ChassisName Like '%Notebook%' Or
    TsysChassisTypes.ChassisName Like '%Laptop%' Or
    TsysChassisTypes.ChassisName Like '%Portable%') Then 'Laptop'
    When (TsysChassisTypes.ChassisName Like '%Mini Tower%' Or
    TsysChassisTypes.ChassisName Like '%Space-Saving%' Or
    TsysChassisTypes.ChassisName Like '%Desktop%' Or
    TsysChassisTypes.ChassisName Like '%Tower%') Then 'Desktop' Else 'Unknown'
  End As [Chassis Type],
  tblAssets.Username,
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  LastUserOn.AssetCount As Computers
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join (Select a.Userdomain,
    a.Username,
    Count(*) As AssetCount
  From tblAssets As a
  Group By a.Userdomain,
    a.Username) As LastUserOn On LastUserOn.Userdomain = tblAssets.Userdomain
    And LastUserOn.Username = tblAssets.Username
Where tblAssetCustom.Warrantydate <= '12/31/2017' And
  TsysChassisTypes.ChassisName Not Like '%Rack%' And
  TsysChassisTypes.ChassisName Not Like '%Main%' And tblAssetCustom.State = 1
Order By TsysChassisTypes.ChassisName,
  tblAssets.AssetName

[tamirt]

thk for make me a report for 1 and 2
but i also need a report for 3 that is:

3.all users that have more then one laptop/desktop and to see what model of laptop/ desktop they have.

[mlachance]

The following will cover requests 1 and 2. Simply filter your results by "Chassis Type" to show all Desktops or all Laptops. This report removes Severs (Rack Mount and Main Systems) from the output. To modify the target warranty expiration date, change <= '12/31/2017' to a different date.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Model,
  Case When (TsysChassisTypes.ChassisName Like '%Docking Station%' Or
    TsysChassisTypes.ChassisName Like '%Notebook%' Or
    TsysChassisTypes.ChassisName Like '%Laptop%' Or
    TsysChassisTypes.ChassisName Like '%Portable%') Then 'Laptop'
    When (TsysChassisTypes.ChassisName Like '%Mini Tower%' Or
    TsysChassisTypes.ChassisName Like '%Space-Saving%' Or
    TsysChassisTypes.ChassisName Like '%Desktop%' Or
    TsysChassisTypes.ChassisName Like '%Tower%') Then 'Desktop' Else 'Unknown'
  End As [Chassis Type],
  tblAssets.Username,
  tblAssetCustom.Warrantydate As [Warranty Expiration]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.Warrantydate <= '12/31/2017' And
  TsysChassisTypes.ChassisName Not Like '%Rack%' And
  TsysChassisTypes.ChassisName Not Like '%Main%' And tblAssetCustom.State = 1
Order By TsysChassisTypes.ChassisName,
  tblAssets.AssetName