Need help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with a query

Author

Topic

jones.michael
Starting Member

2 Posts

Posted - 2014-12-05 : 17:52:56

Hello all,

This is my first post, so go easy on me! :)

I'm brand new to SQL, and I'm having an issue generating a query. It seems like it should be easy, but I just can't figure it out. Basically, I'm trying to query against a collection of computers. (We have SCCM in our environment, so I'm querying against the SCCM database.) Here's what I'm trying to accomplish:

I basically want to have a column named 'Antivirus Protection'. If the query detects that Symantec is installed, I want to insert a 'Yes' in the 'Antivirus Protection' column. If Symantec is not detected by the query, I want to insert a 'No' in the column. I can have the query return machines with Symantec installed; however, it won't return the machines without Symantec installed, and vice versa. Here's an example of the query that I have so far:


SELECT DISTINCT v_R_System.Name0 as 'Computername',
case 
when arp.DisplayName0 like 'symantec%' then 'Yes'
else
'No'
end as 'AntiVirus Protection'

FROM v_R_System
join v_Add_Remove_Programs arp on arp.ResourceID = v_R_System.ResourceID

Because this is querying the Add Remove Programs table (which contains a large number of items) I'm getting a 'Yes' and a 'No' in my return. I'd like to use something like a 'contains' statement, so that I can basically say (in layman's terms) "if this table contains Symantec, then put a 'Yes' in the column, else put a 'No' in the column."

Sorry if this question is poorly constructed; it's the end of the day, and I'm exhausted.

Thanks in advance for anyone who wants to take a stab at it!

Michael

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-05 : 17:58:03

Can you show us some sample data? I'm failing to see what's wrong with your query. Show us some rows of what it is currently outputting (including the ones you don't want to see) and then show us what it should be returning (only correct rows).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mandm
Posting Yak Master

120 Posts

Posted - 2014-12-05 : 20:59:53

I think I understand the problem. Since the v_Add_Remove_Programs table contains rows referencing software other than Symantec for the same ResourceID it will produce a Yes and a No for the same system. Try the following and see if that gets you what you need.

WITH Add_Remove_Programs_CTE (ResourceID)
AS
(

SELECT ResourceID
FROM v_Add_Remove_Programs
WHERE DisplayName0 LIKE 'symantec%'

)
SELECT DISTINCT v_R_System.Name0 as 'Computername'
, case when arp.ResourceID IS NOT NULL
then 'Yes'
else 'No'
end as 'AntiVirus Protection'
FROM v_R_System AS vrs
LEFT JOIN Add_Remove_Programs_CTE AS arp
ON vrs.ResourceID = arp.ResourceID

jones.michael
Starting Member

2 Posts

Posted - 2014-12-08 : 09:51:40

Thanks tkizer and mandm for your swift replies and willingness to help.

@mandm, your solution is spot on! I tested it and it works great! Thanks so much for the help!

Michael

mandm
Posting Yak Master

120 Posts

Posted - 2014-12-08 : 11:20:41

You're welcome. Glad it worked for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources