10-20-2023 02:10 PM - last edited on 04-01-2024 03:22 PM by Mercedes_O
issue while creating an asset report with the user's department ,i can see only Laptop not desktop and i want to see both, code is attached.
Solved! Go to Solution.
10-20-2023 04:00 PM
i manage to fix myself by creating left join on Username and Userdomain as seen in the report ,i believe very good report now can enable lots of things in one report, Sharing the code if someone also need a help.
Select Top (1000000) Case
When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
'Desktop'
Else 'Laptop'
End As [Desktop/Laptop],
tblAssets.Username,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADusers.Department,
tblADComputers.OU
From tblAssets
Left Outer 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
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tblPortableBattery On
tblAssets.AssetID = tblPortableBattery.AssetID
Left Outer Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
tblAssetCustom.Model Not Like '%Virtual%') And
tblAssets.Lastseen Is Not Null And tblAssets.Lastseen <> '' And
tsysAssetTypes.AssetTypename In ('Windows', 'Windows CE') And
tblState.Statename = 'Active'
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10),
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblOperatingsystem.Version,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
'Desktop'
Else 'Laptop'
End,
tsysAssetTypes.AssetTypename,
tblADComputers.OU,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADusers.Department
Order By [Desktop/Laptop],
tblAssets.Username,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADusers.Department,
tblAssets.Domain
10-20-2023 04:09 PM
Thank you for taking time to provide the answer to others
10-20-2023 04:00 PM
i manage to fix myself by creating left join on Username and Userdomain as seen in the report ,i believe very good report now can enable lots of things in one report, Sharing the code if someone also need a help.
Select Top (1000000) Case
When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
'Desktop'
Else 'Laptop'
End As [Desktop/Laptop],
tblAssets.Username,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADusers.Department,
tblADComputers.OU
From tblAssets
Left Outer 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
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tblPortableBattery On
tblAssets.AssetID = tblPortableBattery.AssetID
Left Outer Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
tblAssetCustom.Model Not Like '%Virtual%') And
tblAssets.Lastseen Is Not Null And tblAssets.Lastseen <> '' And
tsysAssetTypes.AssetTypename In ('Windows', 'Windows CE') And
tblState.Statename = 'Active'
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10),
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblOperatingsystem.Version,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
'Desktop'
Else 'Laptop'
End,
tsysAssetTypes.AssetTypename,
tblADComputers.OU,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADusers.Department
Order By [Desktop/Laptop],
tblAssets.Username,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADusers.Department,
tblAssets.Domain
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now