Skip to content

Consolidating query/report data output

Featured Replies

  • Author

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'
  • Replies 27
  • Views 14.2k
  • Created
  • Last Reply

Top Posters In This Topic

Posted Images

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'
  • Author

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.

  • Author

 

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.

  • Author

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]
  • Author

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]

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
  • Author

I get a bunch of errors with that code.

 

post-10749-0-77267200-1446134831.png

Join the conversation

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

Guest
Reply to this topic...

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.