cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Starbase12
Engaged Sweeper
Hello together,

I'm looking for an working report that lists all hostnames of computers that have installed a specific combination of 3 programs with specific versions.

Unfortunatly as soon as I add a second software as requirement, no clients will be listed anymore. I don't know what I'm doing wrong.

For the beginning I tried to list computers, who're having "Microsoft Visual C++ 2008 Redistributable - x86" and "Windows Internet Explorer 11" installed. Because I'm failing here, I didn't try to add a specific version as well. Howevery that's actually the goal: To list computers that have Software A with version 1.x installed + Software B with Version 3.2.x + Software C with Version 0.1.0.

My report so far:

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblSoftwareUni.softwareName
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tblSoftware On tblassets.AssetID =
tblSoftware.AssetID
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName =
'Microsoft Visual C++ 2008 Redistributable - x86' And
tblSoftwareUni.softwareName = 'Windows Internet Explorer 11' And
tblassetcustom.State = 1


Thanks in advanced.
Chris
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Something like this? Just add a check for tblSoftware.SoftwareVersion on each software check.

View solution in original post

4 REPLIES 4
RCorbeil
Honored Sweeper II
There are often many ways of phrasing your query, so if what you've got works, good on ya. Personally, I would have set up the JOINs a little differently that you.

You've set up to match against the software name but select any recorded version, then you're filtering for only the versions you want in the main SELECT. Since you're after specific versions, I would have included those in the sub-SELECTS that you're JOINing against.
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype

Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SAP Interactive Excel%'
And tblSoftware.softwareVersion = '3.0.8.20036'
) As s1 On s1.AssetID = tblassets.AssetID

Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SAP Business Explorer%'
And tblSoftware.softwareVersion = '7.60'
) As s2 On s2.AssetID = tblassets.AssetID

Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%SAP GUI for Windows 7.60 (Patch 5)%'
And tblSoftware.softwareVersion = '7.60 Compilation 1'
) As s3 On s3.AssetID = tblassets.AssetID

Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SAP Business Client 7.0%'
And tblSoftware.softwareVersion = '7.0 PL10'
) As s4 On s4.AssetID = tblassets.AssetID

Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblassets.OScode
Where
tblassetcustom.State = 1
Order By
tblassets.AssetName

Among other things, that simplifies your new request. For each JOINed sub-SELECT, if there's a match for the software and version, then there is an AssetID returned. If there is no match, NULL is returned. You can use this in your WHERE clause:
Where
tblassetcustom.State = 1
AND s1.AssetID IS NOT NULL -- match found
AND s2.AssetID IS NOT NULL -- match found
AND s3.AssetID IS NOT NULL -- match found
AND s4.AssetID IS NULL -- no match

RCorbeil
Honored Sweeper II
Something like this? Just add a check for tblSoftware.SoftwareVersion on each software check.
The report works great. Is there any chance I can also find computers like: List all Computer which has Software 1, Software 2 and Software 3 installed but NOT Software 4?

RC62N wrote:
Something like this? Just add a check for tblSoftware.SoftwareVersion on each software check.


Many thanks. I was able to adapt that report:

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tsysOS.OSname As OS,
tblassets.Version As Build,
tblassets.Username,
s1.SoftwarePublisher As [soft1 Publisher],
s1.softwareName As [soft1 Name],
s1.softwareVersion As [soft1 Version],
s2.SoftwarePublisher As [soft2 Publisher],
s2.softwareName As [soft2 Name],
s2.softwareVersion As [soft2 Version],
s3.SoftwarePublisher As [soft3 Publisher],
s3.softwareName As [soft3 Name],
s3.softwareVersion As [soft3 Version],
s4.SoftwarePublisher As [soft4 Publisher],
s4.softwareName As [soft4 Name],
s4.softwareVersion As [soft4 Version],
tblassets.Lastseen,
tblassets.Lasttried
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SAP Interactive Excel%') As s1 On
s1.AssetID = tblassets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SAP Business Explorer%') As s2 On
s2.AssetID = tblassets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%SAP GUI for Windows 7.60 (Patch 5)%')
As s3 On s3.AssetID = tblassets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SAP Business Client 7.0%') As s4 On
s4.AssetID = tblassets.AssetID
Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblassets.OScode
Where s1.softwareName = 'SAP Interactive Excel' And s1.softwareVersion =
'3.0.8.20036' And s2.softwareName = 'SAP Business Explorer' And
s2.softwareVersion = '7.60' And
s3.softwareName = 'SAP GUI for Windows 7.60 (Patch 5)' And
s3.softwareVersion = '7.60 Compilation 1' And s4.softwareName =
'SAP Business Client 7.0' And s4.softwareVersion = '7.0 PL10' And
tblassetcustom.State = 1
Order By tblassets.AssetName