OU Report

[GreenCup]

Hello,
I want to be able to pull a report, based on specific OU, with the following information in it
Site(OU) | Computer Name | Model | Serial# | OS | Last recorded user with date | Warranty Info

Ultimately, I would want a report for each OU I have, so the OU would be a variable. The report would show only computers in the 'XYZ' OU and then list the other information.

Can I get an example query to use that would produce this information or something close please?

Thanks!

[Hemoco]

Could you please clarify what your report is meant to list.

[GreenCup]

I have been getting duplicated AssetNames from the below report, and am trying to filter that by using the line: Row_Number() Over (Partition By dbo.tblCPlogoninfo.AssetId Order By
dbo.tblCPlogoninfo.logontime Desc) As Seq
As the query is now, it just returns a column named Seq with the row values, as that shows me that is what is creating the duplicates, but I would like to filter my results to show only rows where Seq = 1

Can't seem to figure out how to get that to work. Suggestions?




Select Top 1000000 tblCPlogoninfo.AssetID,
tblAssets.AssetName,
tblComputerSystemProduct.Version As [Vendor Model],
tblAssetCustom.Model As Type,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer As Vendor,
SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
DateDiff(day, SubQuery.LastLogon, GetDate()) As 'Days',
tblOperatingsystem.Caption,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblAssetCustom.Warrantydate,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblADComputers.OU,
Row_Number() Over (Partition By dbo.tblCPlogoninfo.AssetId Order By
dbo.tblCPlogoninfo.logontime Desc) As Seq
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain

[Hemoco]

If you plan on running this for a lot of OUs, it would be better to filter the OU within the report results, instead of editing the report each time.

[GreenCup]

Got exactly what I wanted with this This brings in Last logged in user with timestamp, assetname with hyperlink to asset page, Vendor Specific model, Model #, Serial #, OS, ServicePack, Warranty Date, and Lastseen. The WHERE statement at the bottom can be changed to match the OU you want to pull the information from.

Select Top 1000000 tblCPlogoninfo.AssetID,
tblAssets.AssetName,
tblComputerSystemProduct.Version As [Vendor Model],
tblAssetCustom.Model As Type,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer As Vendor,
SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblOperatingsystem.Caption,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblAssetCustom.Warrantydate,
tblAssets.IPAddress,
tblAssets.Lastseen
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where
tblADComputers.OU =
'OU=Computers,OU=XYZ,OU=North America,OU=ABC,DC=GGG,DC=BBB,DC=com'

[Jbl0]

Thank you for this. Every once in awhile, someone shares the exact answer to a question as posed.  It's amazing how often "answers" include advice on changing the question : P You have provided here just what I needed to do this morning.  Yay to you GreenCup!

[Hemoco · Accepted solution]

You can run a basic report for all of your OUs and then filter the OU within the report results. A sample report can be seen below.

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 tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName