→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
sammy-m
Engaged Sweeper
Hi everybody,

I try to generate a new report but it doesn´t work.
What i want is a report from PCs that have both versions of Java installed. The 7u21 and the 6u20.
I try it but fail. Got the problem that i couldn´t query the same table with the "and" link.
Could someone tell me how to?

regards

samuel
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Lastseen
From
tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole < 2
AND Exists (SELECT tblSoftware.AssetID
FROM tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE tblSoftware.AssetID = tblAssets.AssetID
AND tblSoftwareUni.SoftwareName = 'Java 7 Update 21')
AND Exists (SELECT tblSoftware.AssetID
FROM tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE tblSoftware.AssetID = tblAssets.AssetID
AND tblSoftwareUni.SoftwareName = 'Java(TM) 6 Update 20')
Order By
tblAssets.AssetName


If you have 64-bit versions, change the ='Java blah' to LIKE 'Java blah%' so that you're matching the start of the name.

View solution in original post

3 REPLIES 3
sammy-m
Engaged Sweeper
Allrighty!
Works like a sharm.
Thanks!
sammy-m
Engaged Sweeper
Thanks for the quick answer!
I will check it out when back in office and give a feedback.

Regards

Samuel
RCorbeil
Honored Sweeper II
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Lastseen
From
tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole < 2
AND Exists (SELECT tblSoftware.AssetID
FROM tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE tblSoftware.AssetID = tblAssets.AssetID
AND tblSoftwareUni.SoftwareName = 'Java 7 Update 21')
AND Exists (SELECT tblSoftware.AssetID
FROM tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE tblSoftware.AssetID = tblAssets.AssetID
AND tblSoftwareUni.SoftwareName = 'Java(TM) 6 Update 20')
Order By
tblAssets.AssetName


If you have 64-bit versions, change the ='Java blah' to LIKE 'Java blah%' so that you're matching the start of the name.