Aside from what Garth said cause that could present another challenge, you don't need it in 2 separate reports. You can use a sub-select, but then leverage the use of UNION to create 2 separate queries that return one table.
To illustrate, I am not going to use all the tables in your original query, just the important ones. The key is to have the same columns in both queries or else the UNION won't work. So after the UNION, I use a sub-select to return all machines that don't have any of your .NET versions. In addition, I have it return a column that will always contain NULL. So, you will have almost 2 identical queries, except the latter one will have the sub-select. Then use UNION to combine them. Remember, both queries have to return the same columns names. BTW - when I do a sub-select, I just look for the Resource ID. Hope that helps. Elias select Distinct S.Name0, ARP.DisplayName0 as 'DOTNET' from v_R_System S join v_Add_Remove_Programs ARP on S.ResourceID=ARP.ResourceID where (ARP.DisplayName0 LIKE 'Microsoft .NET Framework 4.5' OR ARP.DisplayName0 LIKE 'Microsoft .NET Framework 4.5.1' OR ARP.DisplayName0 LIKE 'Microsoft .NET Framework 4.5.2') UNION select Distinct S.Name0, NULL as 'DOTNET' from v_R_System S <---- RETURN A NULL IN THE DOTNET COLUMN TO INDICATE IT DOES NOT ANY OF THE .NET VERSIONS join v_Add_Remove_Programs ARP on S.ResourceID=ARP.ResourceID where ARP.ResourceID not in ( select Distinct ARP.ResourceID from v_Add_Remove_Programs ARP where (ARP.DisplayName0 LIKE 'Microsoft .NET Framework 4.5' OR ARP.DisplayName0 LIKE 'Microsoft .NET Framework 4.5.1' OR ARP.DisplayName0 LIKE 'Microsoft .NET Framework 4.5.2')) On Thu, Jul 31, 2014 at 4:22 PM, Atkinson, Matt < [email protected]> wrote: > I’ve been asked to create a report listing the OS, IE version and > whether or not .net 4.5 is installed on a huge list of machines in our > environments. > > > > I’ve so far been fumbling through creating the report and have come up > with this SQL query: > > > > SELECT Distinct dbo.v_R_System.Name0, dbo.v_GS_SoftwareFile.FileName, > LEFT(dbo.v_GS_SoftwareFile.FileVersion, 2) AS 'IE Version', > > dbo.v_Add_Remove_Programs.DisplayName0, > dbo.v_GS_OPERATING_SYSTEM.Caption0 > > FROM dbo.v_R_System INNER JOIN > > dbo.v_GS_SoftwareFile ON > dbo.v_R_System.ResourceID = dbo.v_GS_SoftwareFile.ResourceID INNER JOIN > > dbo.v_Add_Remove_Programs ON > dbo.v_GS_SoftwareFile.ResourceID = dbo.v_Add_Remove_Programs.ResourceID > INNER JOIN > > dbo.v_GS_OPERATING_SYSTEM ON > dbo.v_Add_Remove_Programs.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID > > WHERE (dbo.v_GS_SoftwareFile.FileName = 'iexplore.exe') AND > (dbo.v_GS_SoftwareFile.FilePath LIKE '%C:\Program Files\Internet > Explorer%') AND > > (dbo.v_Add_Remove_Programs.DisplayName0 LIKE > 'Microsoft .NET Framework 4.5' OR > > dbo.v_Add_Remove_Programs.DisplayName0 LIKE > 'Microsoft .NET Framework 4.5.1' OR > > dbo.v_Add_Remove_Programs.DisplayName0 LIKE > 'Microsoft .NET Framework 4.5.2') > > > > The problem that I’m running in to now is that when I run the report I > only get machines that have .net 4.5 installed. Is there a way to get the > rest of the machines to either show up with a blank for the .net version > column, or with just their newest installed version? I tried replacing all > of the .net framework filters with just a single one saying LIKE > 'Microsoft .NET Framework%', but then I get computers repeating in the > report for every version of .net that they have installed. > > > > I could probably figure out the sub-select syntax to get all of the > machines not in the report as it is now, but would they have to go on 2 > different reports? I’d like to have it all in one if possible. I’ll also > need to get this to prompt for a collection name when ran, I’ll put all the > machines that I need to check in a single collection. > > > > -Matt > > > > > > ------------------------------ > > This message is intended for the sole use of the addressee, and may > contain information that is privileged, confidential and exempt from > disclosure under applicable law. If you are not the addressee you are > hereby notified that you may not use, copy, disclose, or distribute to > anyone the message or any information contained in the message. If you have > received this message in error, please immediately advise the sender by > reply email and delete this message. > >
