→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tamirt
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Niko0
Engaged Sweeper III
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

View solution in original post

3 REPLIES 3
Niko0
Engaged Sweeper III
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
Engaged Sweeper II
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
Engaged Sweeper III
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