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'

[GarthMJ]

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.

[GarthMJ]

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'

[P@docIT]

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.

[P@docIT]

 

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.

[P@docIT]

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]

[P@docIT]

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]

[GarthMJ]

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

[P@docIT]

I get a bunch of errors with that code.

 

[GarthMJ]

What is the error?

[P@docIT]

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 1
Invalid 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.

[GarthMJ]

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

[P@docIT]

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.

 

[GarthMJ]

Dumb question but are you connect to the CM12 database or the Master db?

[P@docIT]

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.