cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
GreenCup
Engaged Sweeper II
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!



1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

5 REPLIES 5
Hemoco
Lansweeper Alumni
Could you please clarify what your report is meant to list.
GreenCup
Engaged Sweeper II

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
Lansweeper Alumni
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
Engaged Sweeper II
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'
Hemoco
Lansweeper Alumni
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now