Create a report- Java Runtime Environment by version

[SimplyClueless]

Hello! I have another whacy report I would really like- I am pretty bad at this but maybe someone can help. I sure hope so.

I wonder- is there a way; to write a report- that shows every pc that has JavaRuntime environment loaded- with it's corrosponding version?

how about a report that shows systems with NO java?


----- and; how about ditto with Adobe flash player? systems by version, or no flash player?


we are going through a security audit and I have been tasked to get everything up to date- if this post doesn't have inforamtion needed- please let me know;

I'm slow but I get there...

[SimplyClueless]

Man everyone here is super helpful thanks- testing them now-

[RCorbeil]

Same thing for Java.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
  tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2   -- only interested in workstations, not servers
  AND tblAssets.AssetID NOT IN (SELECT
                                  tblSoftware.AssetID
                                FROM
                                  tblSoftware
                                  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
                                WHERE
                                  tblSoftwareUni.softwareName LIKE 'Java%'
                                  AND (tblSoftwareUni.SoftwarePublisher LIKE 'Oracle%'
                                       OR tblSoftwareUni.SoftwarePublisher LIKE 'Sun Micro%'))
ORDER BY
  tblAssets.AssetUnique

[RCorbeil]

Doing a negative list isn't difficult. Basically, you build a subquery that creates a positive list of asset IDs (assets that DO have software_title), then make your main query check that "this asset isn't in the positive results list".

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
  tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2   -- only interested in workstations, not servers
  AND tblAssets.AssetID NOT IN (SELECT
                                  tblSoftware.AssetID
                                FROM
                                  tblSoftware
                                  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
                                WHERE
                                  tblSoftwareUni.softwareName LIKE '%Flash Player%'
                                  AND tblSoftwareUni.SoftwarePublisher LIKE 'Adobe Systems%')
ORDER BY
  tblAssets.AssetUnique

[RCorbeil]

Minor variation on a theme: Adobe Flash Player installed.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
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 tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
  tblAssetCustom.State = 1
  AND tblComputersystem.Domainrole < 2           -- only interested in workstations, not servers
  AND tblSoftwareUni.softwareName LIKE '%Flash Player%'
  AND tblSoftwareUni.SoftwarePublisher LIKE 'Adobe Systems%'
ORDER BY
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblAssets.AssetUnique

[RCorbeil · Accepted solution]

Any Oracle or Sun Java installed. You should be able to refine it if it produces more than what you're looking for.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
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 tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
  tblAssetCustom.State = 1
  AND tblComputersystem.Domainrole < 2           -- only interested in workstations, not servers
  AND tblSoftwareUni.softwareName LIKE 'Java%'
  AND (tblSoftwareUni.SoftwarePublisher LIKE 'Oracle%'
       OR tblSoftwareUni.SoftwarePublisher LIKE 'Sun Micro%')
ORDER BY
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblAssets.AssetUnique