Software report - all installed software on computers with OU info

[SAAcanders]

Don't know if this is even possible, I sure can't get it to work

I'm being asked to create a report that contains

Computer Name- AD Username- Software Name-Software Version-Computer OU

Any help would be *very* much appreciated 

[Mercedes_O]

@SAAcanders did you try the above suggestion from @rom?  remember to 'accept as solution' if so

[rom · Accepted solution]

I took 'Assets: Installed software by computer' report, and LEFT OUTER JOIN'ed the tblADComputers table, to grab the OU.  This report gets Linux and Mac stuff too, so if you have any of that, the OU will be blank unless they are domain-joined.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Coalesce(tsysOS.OSname, tblSccmAsset.OsCaption,
  tblSccmAsset.OperatingSystemNameandVersion) As OS,
  tblAssets.SP,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblADComputers.OU,
  tblSoftware.Lastchanged
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
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
  Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetId
Where tblState.Statename = 'Active'
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblMacOSInfo.SystemVersion As OS,
  tblAssets.SP,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblSoftwareUni.softwareName As Software,
  tblMacApplications.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblADComputers.OU,
  tblMacApplications.LastChanged
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
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblMacApplications On
      tblAssets.AssetID = tblMacApplications.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblMacApplications.softid
  Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
  Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetId
Where tblState.Statename = 'Active'
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblLinuxSystem.OSRelease As OS,
  tblAssets.SP,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblSoftwareUni.softwareName As Software,
  tblLinuxSoftware.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblADComputers.OU,
  tblLinuxSoftware.LastChanged
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
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
  Inner Join tblSoftwareUni On
      tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
  Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetId
Where tblState.Statename = 'Active'
Order By Domain,
  AssetName,
  Software