Home » Groups » Asset & CMDB

Finding Machines with a Particular Application Installed

Finding Machines with a Particular Application Installed
Filed under: Inventory Solution Reporting
eshwar's picture
Submitted by eshwar on 13 May, 2008 - 10:08.

You can find whether a software package is installed on computers on you network by querying the [Inv_AeX_OS_Add_Remove_Programs] table.

Here's how to find the machine name by joining the [Inv_AeX_AC_Identification] table.

QUERY:

SELECT DISTINCT(ACI.[Name]) AS 'Machine Name', ACI.[Domain] AS 'Domain', ARP.[Name] AS 'Application Name'
FROM  [Inv_AeX_AC_Identification] ACI 
INNER JOIN [Inv_AeX_OS_Add_Remove_Programs] ARP 
ON ACI.[_ResourceGuid] = ARP.[_ResourceGuid] 
WHERE ARP.[Name] LIKE '%Application Name%' 
ORDER BY ACI.[Name]
3.8125
Average: 3.8 (48 votes)

Uninstall Path

Submitted by mszczesniak on 13 May, 2008 - 14:18.

There is also great field "Uninstall Path" that very useful when you need to create uninstall scripts for these applications. For msi packages you just need to modify /i to /x and add /qn on the end - and your script is ready (for Deployment Solution, Task Server or Software Delivery).

Simpler way

Submitted by gebhartj on 14 May, 2008 - 11:55.

Depending on what you need for performance, I recommend joining the vComputer view to get machine name. Your query becomes

SELECT DISTINCT(vc.[Name]) AS 'Machine Name', vc.[Domain] AS 'Domain', ARP.[Name] AS 'Application Name'
FROM vComputer vc
INNER JOIN [Inv_AeX_OS_Add_Remove_Programs] ARP
ON vc.Guid = ARP.[_ResourceGuid]
WHERE ARP.[Name] LIKE '%Application Name%'
ORDER BY vc.[Name]

Some advantages of vComputer:
If you use "Status" (like retired), it automagically filters out everything but "Active"
It brings back other things like IP Address as well

Disadvantage:
For large databases it can be SLOW. It does a couple of "Top 1" queries as part of it intrinsically, so they can tend to bog down. Never use vComputer for collections for precisely this reason.

Alternate method for user - Primary User

Stu Harris's picture
Submitted by Stu Harris on 28 May, 2008 - 15:44.

You can also use Primary User by joining the Inv_AeX_AC_Primary_User table. This table will list the primary user per month, so you'd need to set the month. An easy way to do that is to add in the WHERE section of the query:

[Inv_AeX_AC_Primary_User].[Month] = (select datename(month, getdate()))

What about file Extensions

Submitted by itssissy on 23 July, 2008 - 07:36.

This is great and I have used it multiple times already. But now I need to do the same thing but I need to search for computers with a specific file extension on the computer hard drive. Basic list of computers with *.XXX on their hard drive.

Ideas???

A little different...

Submitted by KSchroeder on 24 July, 2008 - 08:49.

itssissy:
You can do this, but you will need to make some modifications to your Inventory Solution configuration (assuming you're not already inventorying "*.xxx" extension). There are several steps involved, the first of which is documented in AKB 31249.

Then you'll need to write a report against the Inv_AeX_SW_Audit_Software view to search for the .xxx extension, something like:

SELECT DISTINCT(ACI.[Name]) AS 'Machine Name', 
ACI.[Domain] AS 'Domain', sw.[File Name], 
sw.[File Size], sw.[File Path], sw.[File Extension]
FROM  [Inv_AeX_AC_Identification] ACI 
INNER JOIN [Inv_AeX_SW_Audit_Software] sw
ON ACI.[_ResourceGuid] = sw.[_ResourceGuid] 
WHERE sw.[File Extension] LIKE '%Extension%' 
ORDER BY ACI.[Name]

You could clone your existing report and rename the "Application" parameter to "Extension".