Consolidating query/report data output

[P@docIT]

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

[GarthMJ]

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.

[GarthMJ]

BTW, I'm presenting at http://www.mmsmoa.com/, on reporting and CM12. You can still register for this event.

[P@docIT]

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.

[GarthMJ]

#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

[P@docIT]

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?

[GarthMJ]

yes the first two links on the page are the ones to look at. They are examples of what you are looking to do.

[P@docIT]

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'

[GarthMJ]

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.

[P@docIT]

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?

[P@docIT]

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'