PC Build Report Missing Software

[Andy_Sismey]

Hi,

I'm trying to write a report to monitor the quality of our builds during our Windows 10 Refresh, we have a list of "Standard" Software and for each Windows 10 Asset I want to have Yes / No for each Software Application to identify whether or not its installed, also highlight any row which has a NO.
I have managed to write the basic report with help from your code snippets, but cant highlight the rows with No and sort but just the "No"'s.

I am also trying to write an additional report to show a count of each of the software Yes and No, so :

----------- Yes No
Chrome 10 1
Firefox 7 45
etc

Any help would be great, my code so far:

Select Top 1000000 tblAssets.AssetName,
tsysOS.OSname,
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Software1%')
Then 'YES'
Else 'NO'
End As [Software1],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Software2%') Then 'YES'
Else 'NO'
End As [Software2],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Google Chrome%') Then 'YES'
Else 'NO'
End As Chrome,
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Microsoft Visual C++ 2005%') Then
'YES'
Else 'NO'
End As [C++ 2005],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Microsoft Visual C++ 2008%') Then
'YES'
Else 'NO'
End As [C++ 2008],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Microsoft Visual C++ 2010%') Then
'YES'
Else 'NO'
End As [C++ 2010],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Microsoft Visual C++ 2012%') Then
'YES'
Else 'NO'
End As [C++ 2012],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Microsoft Visual C++ 2013%') Then
'YES'
Else 'NO'
End As [C++ 2013],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Microsoft Visual C++ 2017%') Then
'YES'
Else 'NO'
End As [C++ 2017],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Software 8%') Then 'YES'
Else 'NO'
End As [Software 8],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Software 9%') Then 'YES'
Else 'NO'
End As [Software 9],
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like
'%Microsoft Office Professional Plus 2010%') Then 'YES'
Else 'NO'
End As [Office 2010 Pro],

Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like '%Software10%') Then 'YES'
Else 'NO'
End As Software10,

tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Order By tblAssets.Firstseen Desc

[Andy_Sismey]

Thank you so much the report works perfectly and I agree Excel would be a much easier option, thank you once again , I have spent hours on this one !

[RCorbeil]

As far as the software/yes/no totals you're looking for, I'm wondering whether it's worth the effort of working out the logic for a report. Among other things, you'd need to take into account the fact that any given computer could have more than one C++ redistributable for any given year/version (both x86 and x64 could be present).

Might it be simpler to run the big yes/no report, export the result to Excel, then add two formulas below each column counting the yeses and nos? e.g. =COUNTIF(C2:C124,"=YES") and =COUNTIF(C2:C124,"=NO").

Not to say it couldn't be done, but sometimes the simple solution is good enough.

[RCorbeil]

Try this for your big-table-of-yes-and-no:

SELECT Distinct
  tblAssets.AssetName,
  tsysOS.OSname,
  CASE WHEN soft01.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Software1],
  CASE WHEN soft02.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Software2],
  CASE WHEN soft03.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS Chrome,
  CASE WHEN soft04.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2005],
  CASE WHEN soft05.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2008],
  CASE WHEN soft06.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2010],
  CASE WHEN soft07.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2012],
  CASE WHEN soft08.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2013],
  CASE WHEN soft09.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2017],
  CASE WHEN soft10.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Software 8],
  CASE WHEN soft11.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Software 9],
  CASE WHEN soft12.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Office 2010 Pro],
  CASE WHEN soft13.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS Software10,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  CASE
    WHEN soft01.AssetID IS NULL
      OR soft02.AssetID IS NULL
      OR soft03.AssetID IS NULL
      OR soft04.AssetID IS NULL
      OR soft05.AssetID IS NULL
      OR soft06.AssetID IS NULL
      OR soft07.AssetID IS NULL
      OR soft08.AssetID IS NULL
      OR soft09.AssetID IS NULL
      OR soft10.AssetID IS NULL
      OR soft11.AssetID IS NULL
      OR soft12.AssetID IS NULL
      OR soft13.AssetID IS NULL
    THEN '#ffcccc'
  END As backgroundcolor
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Software1%'
            ) AS soft01 ON soft01.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Software2%'
            ) AS soft02 ON soft02.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Google Chrome%'
            ) AS soft03 ON soft03.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2005%'
            ) AS soft04 ON soft04.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2008%'
            ) AS soft05 ON soft05.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2010%'
            ) AS soft06 ON soft06.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2012%'
            ) AS soft07 ON soft07.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2013%'
            ) AS soft08 ON soft08.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2017%'
            ) AS soft09 ON soft09.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Software 8%'
            ) AS soft10 ON soft10.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Software 9%'
            ) AS soft11 ON soft11.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Office Professional Plus 2010%'
            ) AS soft12 ON soft12.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT
               tblSoftware.AssetID
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE tblSoftwareUni.softwareName LIKE '%Software10%'
            ) AS soft13 ON soft13.AssetID = tblAssets.AssetID
WHERE
  tsysOS.OSname = 'Win 10'
  AND tblAssetCustom.State = 1
ORDER BY
  tblAssets.Firstseen DESC