cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Andy_Sismey
Champion Sweeper III
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
3 REPLIES 3
Andy_Sismey
Champion Sweeper III
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
Honored Sweeper II
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
Honored Sweeper II
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