Report on installed software based on OU

[paultech]

Hi all,

I need to create a report on some machine in an OU in AD which has certain software installed (java/flash/shockwave - to name a few).

Annoyingly, I have done this before, but someone deleted the report and I can't remember for the life in me how i originally structured it (I did it a little while ago).

If anyone has an example handy it would be appreciated.

Many thanks,

Paul

[Hemoco · Accepted solution]

A sample report can be seen below. Replace what we've marked in bold with your own values.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblADComputers.OU,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblADComputers.OU = 'OU=TestOU,DC=lansweeper,DC=local' And
tblSoftwareUni.softwareName Like '%java%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName