A new Flexera Community experience is coming on November 18th, click here for more information.
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
Feb 23, 2021 10:20 AM
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)
Feb 23, 2021 06:32 PM
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.
Feb 23, 2021 02:50 PM
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)
Feb 23, 2021 06:32 PM
Excellent Idea. I'll run water the pipes with my current approach. Once proved out, I'll build the longer lasting approach.
Appreciate it
Feb 23, 2021 07:48 PM
User | Count |
---|---|
8 | |
6 | |
3 | |
3 |