→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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

1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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%'

View solution in original post

2 REPLIES 2
RCorbeil
Honored Sweeper II
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
Honored Sweeper II
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?