→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Clagnuts
Engaged Sweeper II
Hi, I'm trying to create a report to list all active assets in multiple computer OU's and their child OU's. The code I have so far (below) lists some but not all assets but also doubles up on those assets. Any help greatly appreciated.

Thanks


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADComputers.OU,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSname,
tblAssets.SP,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblWarrantyDetails.WarrantyStartDate,
tblWarrantyDetails.WarrantyEndDate,
tblWarrantyDetails.ServiceType
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where
(tblADComputers.OU Like 'OU=Computers,OU=UK,DC=Some,DC=company,DC=com')
Or
(tblADComputers.OU Like
'OU=Win7,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU Like
'OU=WLAN,OU=Win7,OU=Computers,OU=UK,DC=Some,DC=company,DC=com')
1 ACCEPTED SOLUTION
Clagnuts
Engaged Sweeper II
Sorted it myself. Code as below.


Select Top 1000000 tblAssets.AssetName,
tblAssets.IPAddress,
tblADComputers.OU,
tblADComputers.Lastchanged,
tblAssets.Username,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblADComputers.AssetID
From tblADComputers
Inner Join tblAssets On tblAssets.AssetID = dbo.tblADComputers.AssetID
Where
(tblADComputers.OU =
'OU=Win7,OU=Computers,OU=UK,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Win8,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Mac,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Misc,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Tablets,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Test,OU=Win7,OU=Computers,OU=UK,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=RTest,OU=Win7,OU=Computers,OU=UK,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Rooms,OU=Win7,OU=Computers,OU=FR,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Offline,OU=Win7,OU=Computers,OU=FR,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Win7,OU=Computers,OU=FR,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Win7,OU=Computers,OU=US,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Rooms,OU=Win7,OU=Computers,OU=US,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=1234,OU=Win7,OU=Computers,OU=US,DC=Some,DC=company,DC=com'))
Order By tblAssets.AssetName

View solution in original post

1 REPLY 1
Clagnuts
Engaged Sweeper II
Sorted it myself. Code as below.


Select Top 1000000 tblAssets.AssetName,
tblAssets.IPAddress,
tblADComputers.OU,
tblADComputers.Lastchanged,
tblAssets.Username,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblADComputers.AssetID
From tblADComputers
Inner Join tblAssets On tblAssets.AssetID = dbo.tblADComputers.AssetID
Where
(tblADComputers.OU =
'OU=Win7,OU=Computers,OU=UK,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Win8,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Mac,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Misc,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Tablets,OU=Computers,OU=UK,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Test,OU=Win7,OU=Computers,OU=UK,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=RTest,OU=Win7,OU=Computers,OU=UK,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Rooms,OU=Win7,OU=Computers,OU=FR,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=Offline,OU=Win7,OU=Computers,OU=FR,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Win7,OU=Computers,OU=FR,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Win7,OU=Computers,OU=US,DC=Some,DC=company,DC=com')) Or
(tblADComputers.OU =
'OU=Rooms,OU=Win7,OU=Computers,OU=US,DC=Some,DC=company,DC=com') Or
(tblADComputers.OU =
'OU=1234,OU=Win7,OU=Computers,OU=US,DC=Some,DC=company,DC=com'))
Order By tblAssets.AssetName