Notification

Icon
Error

Showing all assets without a department

Posted: Wednesday, April 21, 2021 5:50:36 PM(UTC)
JTempleton

JTempleton

Member Original PosterPosts: 46
0
Like
I tried creating a report to show all assets which have no department showing. It is not happening to all assets, but some assets are showing multiple times. Where did I go wrong in making the report? All fields show the same information.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Domain,
tsysIPLocations.IPLocation,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
As OS,
tblOperatingsystem.Caption As [OS Edition],
tblComputersystem.SystemType,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Cast(Cast(tblAssets.Memory As BigInt) / 1024 As Numeric) As
[Memory size (GB)],
tblAssets.Processor,
tblAssets.Mac As MACAddress,
tblADComputers.OU,
tblState.Statename As State,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssetCustom.Custom1 As Owner,
tblAssetCustom.Custom2 As [HW or VM],
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
Case tblAssetCustom.PreventCleanup
When 0 Then 'No'
When 1 Then 'Yes'
End As PreventCleanup,
tblAssets.Scanserver,
tblAssets.Username,
tblADusers.Displayname
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
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where tsysAssetTypes.AssetTypename Not Like 'Monitor' And
tsysAssetTypes.AssetTypename Not Like 'VOIP%' And tblState.Statename =
'active' And tblAssetCustom.Department Is Null
Order By tblAssets.AssetName
Andy.S
#1Andy.S Member Posts: 74  
posted: 4/22/2021 8:30:43 AM(UTC)
Hi,

Have you tried changing your first line select to ....


Code:
Select Distinct Top 1000000 tblAssets.AssetID,


Active Discussions

Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace   Go to last post Go to first unread
Last post: Yesterday at 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU  
Go to last post Go to first unread
Last post: Yesterday at 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin   Go to last post Go to first unread
Last post: Yesterday at 6:08:42 PM(UTC)
Lansweeper Install Adobe Reader Error
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:46:54 PM(UTC)
Lansweeper Stop deployment
by  Tyler M.   Go to last post Go to first unread
Last post: Yesterday at 1:59:22 PM(UTC)
Lansweeper "Out of memory" in errorlog.txt
by  dua  
Go to last post Go to first unread
Last post: Yesterday at 1:00:42 PM(UTC)
Lansweeper Installation issues
by  Troy   Go to last post Go to first unread
Last post: Yesterday at 9:24:54 AM(UTC)
Lansweeper End of Life Asset Report
by  DJ Bradley  
Go to last post Go to first unread
Last post: 5/11/2021 4:09:33 PM(UTC)