Scanning for Assets with multiple software applications

[aboucher]

Hi all,

I'm not a pro at SQL so I was hoping someone could help me out. I'm looking to make a report that will find me if certain assets have specific kinds of software applications and versions installed. I started playing around with the AND function but the report isn't correct as it's giving me 0 results. Here's what I have now and I've tried with a bunch of different applications, but it always returns 0.

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblSoftwareUni.softwareName As Software,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftware.softwareVersion As Version,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%MozyPro%' And
tblSoftwareUni.softwareName Like '%Spotify%'
Order By tblAssets.Domain,
tblAssets.AssetName

[RCorbeil]

Looking for machines with two pieces of software is a different kettle of fish. Take a look at this thread for one approach.

If you want only those machines with both of the titles you're looking for you can add two conditions to the WHERE clause:

WHERE
  ...
  AND software1.AssetID IS NOT NULL
  AND software2.AssetID IS NOT NULL

Without those, you'll get a list of all assets, regardless of whether they have either title installed.

[RCorbeil]

Look at from the perspective of the computer. It steps through the list of assets, one-by-one. For each asset, it then steps through the software titles one-by-one. Your WHERE conditions are evaluated at each individual pairing of asset + software. As presented, your condition is saying

For this asset, for this specific piece of software, is the software both MozyPro AND Spotify?

If I'm reading your requirement correctly, what you want to be saying is

For this asset, for this specific piece of software, is the software either MozyPro OR Spotify?

For each asset, as the process steps through each associated piece of software, it will then process it...

Software: Microsoft Word; is the software either MozyPro (NO) or Spotify (NO); no yeses means exclude
Software: MozyPro; is the software either MozyPro (YES) or Spotify (NO); we've got a YES, so include in the output
Software: Adobe Reader; is the software either MozyPro (NO) or Spotify (NO); no yeses means exclude
Software: Spotify; is the software either MozyPro (NO) or Spotify (YES); we've got a YES, so include in the output

etc.

Change your WHERE condition from an AND to an OR and see if it produces what you're looking for:

Where
  tblSoftwareUni.softwareName Like '%MozyPro%'
  OR tblSoftwareUni.softwareName Like '%Spotify%'

[aboucher]

Thanks for your response! I'm looking for a way to search for assets that have both MozyPro and Spotify (or other software programs) I changed it to the OR function, but I only get assets that have either of the two. Is there a way to search for assets that have both? I figured an AND function would work, but evidently not.