Wow, this is basically sorcery to me at this point, thanks for the assistance. I need to get some T-SQL training right away.
From: [email protected] [mailto:[email protected]] On Behalf Of Elias Leal Sent: Thursday, July 31, 2014 3:31 PM To: [email protected] Subject: Re: [mssms] Reporting question 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]<mailto:[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. ________________________________ 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.
