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]

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.