Jump to content


  • 0
P@docIT

Consolidating query/report data output

Question

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.

 

post-10749-0-25299900-1445967691.png

 

 

post-10749-0-49798200-1445967306.png

 

Cheers,

Mike

Share this post


Link to post
Share on other sites

Recommended Posts

  • 0

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.

Share this post


Link to post
Share on other sites

  • 0

#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

Share this post


Link to post
Share on other sites

  • 0

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'

post-10749-0-22830700-1446042730_thumb.png

Share this post


Link to post
Share on other sites

  • 0

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.

Share this post


Link to post
Share on other sites

  • 0

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'

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...



×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.