Creating report for specific software with AD username and AD user OU

[danielk]

Hi There,

Please advise how to create a report on who has specific software with the AD username and AD user OU and last seen. Thanks

eg.
acrobat9 ADusername1 ADusername1OU LastSeen
acrobat9 ADusername2 ADusername2OU LastSeen
acrobat9 ADusername3 ADusername3OU LastSeen

[RCorbeil · Accepted solution]

I had a few minutes to kill so decided to assume your answer would be "yes".

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName,
  tblADusers.Name,
  tblADusers.OU
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
  INNER JOIN tblADusers ON tblAssets.Userdomain = tblADusers.Userdomain And tblAssets.Username = tblADusers.Username
WHERE
  tblAssetCustom.State = 1
  AND tblSoftwareUni.softwareName LIKE 'Adobe Acrobat 9%'
ORDER BY
  tblADusers.Name

You'll probably need to do a little work to figure out the filtering you want to apply for the software you want.

If you're not familiar with the software details, I suggest creating a simple query to see what's in the table.

SELECT Top 1000000
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher
FROM
  tblSoftwareUni

Some things you can get away with just checking the software name (e.g. Adobe Acrobat), others require that you check both the name and the publisher.

If you're not familiar with using wildcards for string matches, it boils down to:
- Exactly matches "blah": Softwarename = 'blah'
- Starts with "blah": Softwarename LIKE 'blah%'
- Ends with "blah": Softwarename LIKE '%blah'
- Contains "blah": Softwarename LIKE '%blah%'

[RCorbeil]

The software is tied to the asset (the computer), not the user. The base form of what you're asking for would be a list of computers on which particular software is installed. AD details about the last user of each computer could be produced.

computer1 / Acrobat 9 / ADusername / ADuserOU / last seen
computer8 / Acrobat 9 / ADusername / ADuserOU / last seen
computer42 / Acrobat 9 / ADusername / ADuserOU / last seen

You could sort the output on the user, but realize that if the one person has been the most recent user of multiple machines you'll see multiple results for the.

Does that sound like what you're looking for?