Report Help - Java correlation with known Application with Java dependency

[caldwellb]

All,

Doing this manually today which is very time intensive.  Asking to see if the community here has ideas or SQL Scripts to help. Here's what I'm ideally attempting in order to refresh our data faster

1. List of Applications that I would define in a "List" which we know has a Java dependency and would include device properties to get where installed. The list would be updated periodically

2. The above list would then be compared against known Java deployments to include device properties to get where installed.  

3. A compare that would then show Java submit to remediation or otherwise where known to have one or more application that has a Java dependency

Step 1 is where help needed, my list is growing and not feasible to run individual app deployed reportes

Steps 2,3 I can manage. Just showing end to end in case of some wild miracle this is possible in a complex SQL query

Thanks for any help on step 1

[caldwellb]

I'm making some progress on Step 1.  Shoutout to @jjensen for his SQL Query that got me started

SELECT
cc.ComputerName AS 'Hostname',
cc.IPAddress,
st.FullName AS 'Application',
cc.InventoryDate
FROM InstalledApplications ia
JOIN ComplianceComputer cc ON cc.ComplianceComputerID = ia.ComplianceComputerID
JOIN SoftwareTitle st ON st.SoftwareTitleID = ia.SoftwareTitleID
WHERE FullName IN (
'KNIME Analytics Platform 4.0',
'KNIME Analytics Platform 4.1',
'KNIME Analytics Platform 4.2',
'WebEx Meetings 30',
'WebEx Meetings 32.12'
)

Now to be more efficient any ideas on how to maintain the list or build out first one since I have hundreds likely of the applications "Fullname I need to pull out device names.  then I compare those devices to where I have Java installed I wish to example, then I think I'm there.

[ChrisG · Accepted solution]

If you're comfortable working directly with data in the database, you could consider creating a specialized table to store the application names you're interested in rather than embedding the list in your query. For example:

CREATE TABLE dbo.MyJavaApps(FullName NVARCHAR(512) NOT NULL)

INSERT INTO dbo.MyJavaApps(FullName) VALUES
	('KNIME Analytics Platform 4.0'),
	('KNIME Analytics Platform 4.1'),
	('KNIME Analytics Platform 4.2'),
	('WebEx Meetings 30'),
	('WebEx Meetings 32.12')

SELECT
	cc.ComputerName AS 'Hostname',
	cc.IPAddress,
	st.FullName AS 'Application',
	cc.InventoryDate
FROM dbo.InstalledApplications ia
	JOIN dbo.ComplianceComputer cc ON cc.ComplianceComputerID = ia.ComplianceComputerID
	JOIN dbo.SoftwareTitle st ON st.SoftwareTitleID = ia.SoftwareTitleID
WHERE st.FullName IN (SELECT FullName from dbo.MyJavaApps)

[caldwellb]

Excellent Idea.  I'll run water the pipes with my current approach. Once proved out, I'll build the longer lasting approach. 

Appreciate it