ā11-04-2022 01:50 AM - last edited on ā04-02-2024 11:43 AM by Mercedes_O
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
Solved! Go to Solution.
ā11-04-2022 04:27 PM
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
ā11-10-2022 10:39 AM
@SAAcanders did you try the above suggestion from @rom? remember to 'accept as solution' if so
ā11-04-2022 04:27 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now