myITforum.com Community Forum myITforum.com Community Forum

Home  Forums  Blogs  Live Support chat  Search Articles  Wiki  FAQ  Email Lists  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Photo Gallery  Member List  Search  Calendars  FAQ  Ticket List  Log Out

All Forums RSS Feed Subscription:


  


ConfigMgr/SMS Spyware query and report gets its own Wiki page

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
  Printable Version
All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> ConfigMgr/SMS Spyware query and report gets its own Wiki page Page: [1]
Login
Message << Older Topic   Newer Topic >>
ConfigMgr/SMS Spyware query and report gets its own Wik... - 8/26/2008 1:14:35 PM   
rodtrent


Posts: 1920
Score: 39
Status: offline 		This report and query is sought after time-and-again, so we’ve posted it to the myITforum.com Wiki pages.  This way it can always be found, and also anyone can add to it with a Wiki account.  Feel free to add known Spyware to the current query to help keep it updated.

Here’s the permanent page:

http://www.myitforum.com/myITWiki/SMSSpyware.ashx
Post #: 1
RE: ConfigMgr/SMS Spyware query and report gets its own... - 8/26/2008 1:21:17 PM   
smartin66


Posts: 114
Score: 0
Joined: 2/11/2003
From: Jefferson City, MO
Status: offline 		Rod,
I have cut and pasted this script into an SMS 2003 SP2 Report and Query and SCCM SP1 Report and Query and keep getting errors. What am I doing wrong?
TIA

_____________________________

Sandy Martin

(in reply to rodtrent)
Post #: 2
RE: ConfigMgr/SMS Spyware query and report gets its own... - 8/26/2008 1:25:36 PM   
rodtrent


Posts: 1920
Score: 39
Status: offline 		Did you notice that it is a SQL query?

(in reply to smartin66)
Post #: 3
RE: ConfigMgr/SMS Spyware query and report gets its own... - 8/26/2008 1:34:48 PM   
rodtrent


Posts: 1920
Score: 39
Status: offline 		BTW: I just added a download link in that page, just in case there are formatting problems with the inline code.

(in reply to rodtrent)
Post #: 4
RE: ConfigMgr/SMS Spyware query and report gets its own... - 8/26/2008 2:08:38 PM   
smartin66


Posts: 114
Score: 0
Joined: 2/11/2003
From: Jefferson City, MO
Status: offline 		Thanks, Rod,

I just tried the script in SQL Query Analyzer (SQL 2005) and got the following message:

quote:

Incorrect syntax near the keyword 'group'.

I get the same error when saving this as a report. I downloaded the zip file from the permanent web page, using that copy.

Is anyone else able to run this in Query Analyzer or as a Report?

TIA

< Message edited by smartin66 -- 8/26/2008 2:09:20 PM >

_____________________________

Sandy Martin

(in reply to rodtrent)
Post #: 5
RE: ConfigMgr/SMS Spyware query and report gets its own... - 8/26/2008 2:18:57 PM   
jnelson993


Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		That whole thing's kind of a mess...the group by has to be before the order by, and the group by doesn't include all of the columns that it should to work...actually, there's no aggregate functions in there so I don't see a reason to use the group by at all.  A DISTINCT would do the trick if it's in there to reduce dupes.

Someone want I should fix it?

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to smartin66)
Post #: 6
RE: ConfigMgr/SMS Spyware query and report gets its own... - 8/26/2008 2:59:43 PM   
smartin66


Posts: 114
Score: 0
Joined: 2/11/2003
From: Jefferson City, MO
Status: offline 		John,

I took out the group by clause and it seems to be running. Now I have to figure out how to join the right tables together to request a collection ID, as the results seem large and keep timing out.

When I ran it in our SCCM SP1 test domain, the results were blank. Since we have just 4 computers in our test domain, none of them have any adware/spyware on them, so the blank results are probably accurate. No errors displayed before the blank results were generated.

Thanks for responding. I am guessing that the script needs to be "fixed" so it is correct on the new permanent home that Rod gave it.

Again, thanks for all of your help, guys.


_____________________________

Sandy Martin

(in reply to jnelson993)
Post #: 7
RE: ConfigMgr/SMS Spyware query and report gets its own... - 8/26/2008 3:03:00 PM  2 votes
jnelson993


Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		OK, all done.  Here's my observations:
1) The cost was 4000, that usually indicates a less-than-optimal query, or one that is joining a ton of tables and views and isn't filtered.  I thought we could do better so I moved the filter predicate (all those LIKEs) into the join statement in the FROM and it went down to a cost of 127.  That's a 97% reduction in cost.
2) the GROUP BY needed to be moved above the ORDER BY, but there are no aggregate functions (like MAX or COUNT) so I changed that to DISTINCT at the top and removed the group by.
3) There is a LEFT OUTER JOIN, but the filter criteria for that join is in the WHERE clause which causes that LEFT OUTER to act like an INNER (read -->THIS<--).  So I changed it to inner to keep the behavior of pulling only the machines that HAVE potential malware.
4) The Wiki didn't like the -- comments so I converted to explicitly terminated /* comments */
5) I don't know what I'm doing yet with that Wiki as this is my first edit, so I don't know how to update the DOWNLOAD link or if it does that automatically...

Let me know if this is helpful or if it needs more esplaining.




< Message edited by jnelson993 -- 8/26/2008 3:14:59 PM >

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)
Post #: 8
RE: ConfigMgr/SMS Spyware query and report gets its own... - 8/26/2008 3:16:54 PM   
smartin66


Posts: 114
Score: 0
Joined: 2/11/2003
From: Jefferson City, MO
Status: offline 		Thanks, John,
It worked like a charm and didn't time out, so now I don't have to modify anything.
I love this place!

_____________________________

Sandy Martin

(in reply to jnelson993)
Post #: 9
RE: ConfigMgr/SMS Spyware query and report gets its own... - 11/5/2008 1:07:35 PM   
squeekyfoot

 

Posts: 6
Score: 0
Joined: 10/20/2008
Status: offline 		Thanks for this.  Do you know how to get it to return the IP and MAC for the devices?  I am not having much luck getting it to work.

(in reply to smartin66)
Post #: 10
RE: ConfigMgr/SMS Spyware query and report gets its own... - 11/5/2008 1:33:07 PM   
jnelson993


Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		You COULD do that by joining to dbo.v_RA_System_IPAddresses0  and dbo.v_RA_System_MACAddresses0, but beware, when you do that you will see multiple records per machine because of the multiple addresses.  Might be better to add them using the principles --> HERE <-- where you concatenate the IP and MAC into a single field.

Just a thought.


_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to squeekyfoot)
Post #: 11
RE: ConfigMgr/SMS Spyware query and report gets its own... - 11/5/2008 1:49:06 PM   
squeekyfoot

 

Posts: 6
Score: 0
Joined: 10/20/2008
Status: offline 		That is what I was getting, the multiple records. 

Now to play with the code from that link and see what I can do. 

(in reply to jnelson993)
Post #: 12
Page:   [1]
All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> ConfigMgr/SMS Spyware query and report gets its own Wiki page Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts



  
Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.266