P@docIT Posted October 27, 2015 Report post Posted October 27, 2015 I am completely new to report building and honeslty I can typically fumble my way through basic queries but I have hit a spot where I need a little help. I am trying get the information in the query screen below to output more like what is in the excel screenshot. Right now you get at least two entries per machine for VSE and the agent. There also some machines that have HIPS listed in non 64 installed apps or have the agent in the installed apps 64 list. Is there a way to make the software the header and just the version that is installed on that machine whether its in InstalledApps or InstalledApps_64? Hope that makes sense. Cheers, Mike Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 27, 2015 Report post Posted October 27, 2015 First, it would help if you post a link to your McAfee MOF edits. Second, without your query it is really hard for anyone to help you. Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 27, 2015 Report post Posted October 27, 2015 BTW, I'm presenting at http://www.mmsmoa.com/, on reporting and CM12. You can still register for this event. Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 27, 2015 Report post Posted October 27, 2015 Hey Garth, You will have to forgive my noobness. I'm not sure about the McAfee MOF, i didn't edit any MOF nor do I know how to find/link one if there is one (inherited sccm environment). I'm not super familiar with editing MOF files either. MOF, SQL, and report building are at the top of my list of things to learn. Here is my query statement: select distinct SMS_R_System.NetbiosName, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version, SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS_64.Version, SMS_G_System_CH_ClientSummary.ClientActiveStatus from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.Publisher like "McAfee%" and SMS_G_System_ADD_REMOVE_PROGRAMS_64.Publisher like "McAfee%" I had to end up using "publisher like" because actually referencing the software by name caused 0 results ot be returned. Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 27, 2015 Report post Posted October 27, 2015 #1 The query you have posted will only every find software titles from an x64 computer. The reason is because of your inner joins and the Where conditions. #2 Remember that the query that you post is WQL and NOT SQL. Report builder (RB), SQL Server Business Intelligence Development Studio (BIDS), SQL Server Data Tools for Visual Studio (SSDT) all using SQL. #2, what you are looking for an NOT a simple query. It is at least 4 different queries. These blog will get you started. http://smsug.ca/search/SearchResults.aspx?q=two #3, Break this down to its simple form and only look for 1 thing. Computer and AV Engine then repeat, for each item until you have 4 different queries. #4, Remember the ResourceID is your friend. #5 once you have the 4 queries, create the all encompassing query. See blog posts above. #6 Ignore Report Builder and use BIDS/ SSDT instead. They can do more that RB. Tip: sign up for the Enhansoft newsletter. I will be posting report building tips in the new year. http://www.enhansoft.com/blog Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 27, 2015 Report post Posted October 27, 2015 Thanks Garth. #2 Takes me to a search page at smsug, I see a blog post in that search called "Report Two items from Add/Remove Program" is the blog I should be looking at? Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 27, 2015 Report post Posted October 27, 2015 yes the first two links on the page are the ones to look at. They are examples of what you are looking to do. Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 28, 2015 Report post Posted October 28, 2015 Ok here is what i've come up with so far. I'm sure there is a much better way to do this but still learning. I need to outer join the second select statement so that the Host Intrusion Prevention column shows up at the end of the first set of results, but i'm having a hard time figuring out/understanding the syntax. Also what will show up in rows that have no data (machines that don't have HIPS). Can I tell sql to populate it with specific text, "not installed" for example? Cheers, Mike SELECT System_DATA.Name0 AS [Computer Name], Add_Remove_Programs_DATA.Version00 AS [McAfee VirusScan Enterprise], McAfeeEPO_DATA.Version00 AS [McAfee Agent] FROM dbo.System_DATA INNER JOIN dbo.Add_Remove_Programs_DATA ON System_Data.MachineID=Add_Remove_Programs_DATA.MachineID JOIN dbo.McAfeeEPO_DATA ON System_Data.MachineID=McAfeeEPO_DATA.MachineID WHERE Add_Remove_Programs_DATA.DisplayName00 like '%VirusScan%' AND McAfeeEPO_DATA.SoftwareID00 = 'EPOAGENT3000' SELECT System_DATA.Name0 AS [Computer Name], McAfeeEPO_DATA.Version00 AS [Host Intrusion Prevention] FROM dbo.System_DATA INNER JOIN dbo.Add_Remove_Programs_DATA ON System_Data.MachineID=Add_Remove_Programs_DATA.MachineID JOIN dbo.McAfeeEPO_DATA ON System_Data.MachineID=McAfeeEPO_DATA.MachineID WHERE McAfeeEPO_DATA.ProductName00 = 'Host Intrusion Prevention' Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 28, 2015 Report post Posted October 28, 2015 Here are my notes about the queries and you questions. 1) It is NOT supported to query the table directly, you need to query the views. 2) You are only getting the x86 software titles because you are querying the x86 table only. instead use View. v_add_remove_programs 3) Look at the ISNULL function. 4) Use Outer joins for all queries. BTW, that is a great start. Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 28, 2015 Report post Posted October 28, 2015 Ok need a little more explanation. "v_Add_Remove_Programs" will replace all instances of "Add_Remove_Programs_DATA.column" or just "dbo.Add_Remove_Programs_DATA" or both? Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 28, 2015 Report post Posted October 28, 2015 Ok need a little more explanation. "v_Add_Remove_Programs" will replace all instances of "Add_Remove_Programs_DATA.column" or just "dbo.Add_Remove_Programs_DATA" or both? Figured that part out. Moving on to your other suggestions. Thanks again. SELECT Name0 AS [Computer Name], v_GS_McAfeeEPO0.Version0 AS [McAfee VirusScan Enterprise], v_GS_McAfeeEPO0.Version0 AS [McAfee Agent] FROM v_GS_SYSTEM INNER JOIN v_Add_Remove_Programs ON v_GS_SYSTEM.ResourceID=v_Add_Remove_Programs.ResourceID JOIN v_GS_McAfeeEPO0 ON v_GS_SYSTEM.ResourceID=v_Add_Remove_Programs.ResourceID WHERE v_Add_Remove_Programs.DisplayName0 like '%VirusScan%' AND v_GS_McAfeeEPO0.SoftwareID0 = 'EPOAGENT3000' Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 28, 2015 Report post Posted October 28, 2015 You need to do both. But don't forget that you also need to fix all the other ones too, so that you are only querying the SQL views. Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 28, 2015 Report post Posted October 28, 2015 I'm not sure why you are querying ARP data, when you are not using it within the results. Notice that I fixed the second "inner join" line. BTW, The EPO View is a custom MOF edit. Try this. SELECT RV.Netbios_Name0 AS 'Computer Name', EPO.Version0 AS 'McAfee VirusScan Enterprise', EPO.Version0 AS 'McAfee Agent' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE ARP.DisplayName0 like '%VirusScan%' AND EPO.SoftwareID0 = 'EPOAGENT3000' Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 28, 2015 Report post Posted October 28, 2015 You need to do both. But don't forget that you also need to fix all the other ones too, so that you are only querying the SQL views. Disregard... switched to your syntax, also noticed I had a typo in mine. Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 28, 2015 Report post Posted October 28, 2015 I'm not sure why you are querying ARP data, when you are not using it within the results. Notice that I fixed the second "inner join" line. BTW, The EPO View is a custom MOF edit. Try this. SELECT RV.Netbios_Name0 AS 'Computer Name', EPO.Version0 AS 'McAfee VirusScan Enterprise', EPO.Version0 AS 'McAfee Agent' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE ARP.DisplayName0 like '%VirusScan%' AND EPO.SoftwareID0 = 'EPOAGENT3000' Ok. Good to see how this syntax correlates to what I had helps me better understand whats going on. How would I add an outer join to this to add the HIPS data. Also what does RV stand for? ARP and EPO i get. Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 28, 2015 Report post Posted October 28, 2015 I think I got it. SELECT * FROM (SELECT RV.Netbios_Name0 AS 'Computer Name', ARP.Version0 AS 'McAfee VirusScan Enterprise', EPO.Version0 AS 'McAfee Agent' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE ARP.DisplayName0 like '%VirusScan%' AND EPO.SoftwareID0 = 'EPOAGENT3000') a LEFT OUTER JOIN (SELECT RV.Netbios_Name0 AS 'Computer Name', EPO.Version0 AS 'Host Intrusion Prevention' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE EPO.ProductName0 = 'Host Intrusion Prevention') b ON a.[Computer Name] = b.[Computer Name] Now on to the isNull. EDIT: Dang I just noticed that this shows a second computer name column. Man I have stop posting before looking at the problem.. SELECT a.[Computer Name],a.[McAfee VirusScan Enterprise],a.[McAfee Agent],b.[Host Intrusion Prevention] FROM (SELECT RV.Netbios_Name0 AS 'Computer Name', ARP.Version0 AS 'McAfee VirusScan Enterprise', EPO.Version0 AS 'McAfee Agent' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE ARP.DisplayName0 like '%VirusScan%' AND EPO.SoftwareID0 = 'EPOAGENT3000') a LEFT OUTER JOIN (SELECT RV.Netbios_Name0 AS 'Computer Name', EPO.Version0 AS 'Host Intrusion Prevention' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE EPO.ProductName0 = 'Host Intrusion Prevention') b ON a.[Computer Name] = b.[Computer Name] Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 28, 2015 Report post Posted October 28, 2015 So close... thought i found a solution for the null fields but it doesn't work, the query doesn't fail but it doesn't change the value. ISNULL(NULLIF(EPO.Version0,''),'Not Installed') AS 'Host Intrusion Prevention' SELECT a.[Computer Name],a.[McAfee VirusScan Enterprise],a.[McAfee Agent],b.[Host Intrusion Prevention],c.[EnCase Enterprise Agent] FROM (SELECT RV.Netbios_Name0 AS 'Computer Name', RV.ResourceID AS 'Resource', ARP.Version0 AS 'McAfee VirusScan Enterprise', EPO.Version0 AS 'McAfee Agent' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE ARP.DisplayName0 like '%VirusScan%' AND EPO.SoftwareID0 = 'EPOAGENT3000') a LEFT OUTER JOIN (SELECT RV.Netbios_Name0 AS 'Computer Name', ISNULL(NULLIF(EPO.Version0,''),'Not Installed') AS 'Host Intrusion Prevention' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE EPO.ProductName0 = 'Host Intrusion Prevention') b ON a.[Computer Name] = b.[Computer Name] LEFT OUTER JOIN (SELECT vSMS_G_System_SoftwareFile.ClientId AS 'Resource', vSMS_G_System_SoftwareFile.FileVersion AS 'EnCase Enterprise Agent' FROM dbo.v_R_System_Valid RV INNER JOIN vSMS_G_System_SoftwareFile ON RV.ResourceID = vSMS_G_System_SoftwareFile.ClientId WHERE FileName = 'RDNS.exe') c ON a.Resource = c.Resource ORDER BY [Computer Name] Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 29, 2015 Report post Posted October 29, 2015 Try this, completely untested as I don't have the McAfee mof edit. BTW, can you avoid using line numbers in your post, you have to clean them out before you can use the queries. SELECT RV.Netbios_Name0 as 'Computer Name', isnull(EPO.McAfeeVirusScanEnterprise, 'n/a') as 'VirusScan Enterprise', isnull(EPO.McAfeeAgent, 'n/a') as 'McAfee Agent', isnull(HIP.HostIntrusionPrevention, 'n/a') as 'Host Intrusion Prevention', isnull(RDNS.EnCaseEnterpriseAgent, 'n/a') as 'EnCase Enterprise Agent' FROM dbo.v_R_System_Valid RV LEFT OUTER JOIN ( SELECT RV.Netbios_Name0 AS 'ComputerName', RV.ResourceID AS 'ResourceID', ARP.Version0 AS 'McAfeeVirusScanEnterprise', EPO.Version0 AS 'McAfeeAgent' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE ARP.DisplayName0 like '%VirusScan%' AND EPO.SoftwareID0 = 'EPOAGENT3000' ) EPO ON RV.ResourceID = EPO.ResourceID LEFT OUTER JOIN ( SELECT EPO.ResourceID AS 'ResourceID', EPO.Version0 AS 'HostIntrusionPrevention' FROM dbo.v_GS_McAfeeEPO0 EPO WHERE EPO.ProductName0 = 'Host Intrusion Prevention' ) HIP ON RV.ResourceID = HIP.ResourceID LEFT OUTER JOIN ( SELECT SF.ResourceID AS 'ResourceID', SF.FileVersion AS 'EnCaseEnterpriseAgent' FROM dbo.v_GS_SoftwareFile SF WHERE SF.FileName = 'RDNS.exe' ) RDNS ON RV.ResourceID = RDNS.ResourceID ORDER BY RV.Netbios_Name0 Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 29, 2015 Report post Posted October 29, 2015 I get a bunch of errors with that code. Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 29, 2015 Report post Posted October 29, 2015 What is the error? Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 29, 2015 Report post Posted October 29, 2015 The multi-part identifier "RV.Netbios_Name0" could not be found. Along with all of the other items underlined in red in the screenshot. output error: Msg 208, Level 16, State 1, Line 1Invalid object name 'dbo.v_R_System_Valid'. Hmm interesting. I plugged the sql that I worked on yesterday into sql and executed and I'm getting the same errors about dbo.v_R_System_Valid being an invalid object. I didn't change anything. Don't understand why it worked yesterday but not today. I'm using that same sql in report builder and execting the report seems ot be working. Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 29, 2015 Report post Posted October 29, 2015 I see no reason for you to get this error message. I commented out the McAfee custom mof stuff and the query works great.. How about you? SELECT RV.Netbios_Name0 as 'Computer Name', -- isnull(EPO.McAfeeVirusScanEnterprise, 'n/a') as 'VirusScan Enterprise', -- isnull(EPO.McAfeeAgent, 'n/a') as 'McAfee Agent', -- isnull(HIP.HostIntrusionPrevention, 'n/a') as 'Host Intrusion Prevention', isnull(RDNS.EnCaseEnterpriseAgent, 'n/a') as 'EnCase Enterprise Agent' FROM dbo.v_R_System_Valid RV /* LEFT OUTER JOIN ( SELECT RV.Netbios_Name0 AS 'ComputerName', RV.ResourceID AS 'ResourceID', ARP.Version0 AS 'McAfeeVirusScanEnterprise', EPO.Version0 AS 'McAfeeAgent' FROM dbo.v_R_System_Valid RV INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID INNER JOIN dbo.v_GS_McAfeeEPO0 EPO ON RV.ResourceID = EPO.ResourceID WHERE ARP.DisplayName0 like '%VirusScan%' AND EPO.SoftwareID0 = 'EPOAGENT3000' ) EPO ON RV.ResourceID = EPO.ResourceID LEFT OUTER JOIN ( SELECT EPO.ResourceID AS 'ResourceID', EPO.Version0 AS 'HostIntrusionPrevention' FROM dbo.v_GS_McAfeeEPO0 EPO WHERE EPO.ProductName0 = 'Host Intrusion Prevention' ) HIP ON RV.ResourceID = HIP.ResourceID*/ LEFT OUTER JOIN ( SELECT SF.ResourceID AS 'ResourceID', SF.FileVersion AS 'EnCaseEnterpriseAgent' FROM dbo.v_GS_SoftwareFile SF WHERE SF.FileName = 'RDNS.exe' ) RDNS ON RV.ResourceID = RDNS.ResourceID ORDER BY RV.Netbios_Name0 Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 29, 2015 Report post Posted October 29, 2015 Not sure you can see in the screenshot that dbo.v_R_System_Valid shows up in the oblect explorer. So not sure, server might need a reboot. Quote Share this post Link to post Share on other sites More sharing options...
GarthMJ Posted October 29, 2015 Report post Posted October 29, 2015 Dumb question but are you connect to the CM12 database or the Master db? Quote Share this post Link to post Share on other sites More sharing options...
P@docIT Posted October 29, 2015 Report post Posted October 29, 2015 Not a dumb question at all because I believe that was it. So now that i'm aware of the DB drop down I think my focus was on that drop down when I attempted to scroll in another window, inadvertantly changing the DB. Quote Share this post Link to post Share on other sites More sharing options...