none
SCCM Collection query providing computers with specific Malware Infection RRS feed

  • General discussion

  • Hi,

    I am trying to source for a collection query concerning my environment, my scernario first is this:

    1. SCCM 2007 R3 Primary Site Server

    2. Forefront Endpoint Protection 2010 also installed in the same server (Intergrated with SCCM)

    Now my question is how can I make a query for a Collection generating the computers with the Specific Malware infection for example W32/Sality.Viru, as a current default all reports and collections made by FEP is by defualt read only.

    But since the SCCM and FEP is integrated computers with specifi Malware infection can be quiried but since I am not an sql guy then if any once can help me?

    Regards,

    Sunday, February 19, 2012 12:41 PM

All replies

  • if it is specific file or exe ,you can try to find that by enabling software inventory option and then create collection based on the file name.

    http://eskonr.com/2011/05/sccm-report-to-inventory-file-types-like-pst-ost-sft-etc/

    else if you are looking for application installed or not,try with default report avilable computers with particular add and remove programs.


    Please click on "vote as Helpful" if you feel this post helpful to you.

    Eswar Koneti | My Tech blog: www.eskonr.com| Linkedin: Eswar Koneti


    Sunday, February 19, 2012 1:59 PM
  • Hi Arnel,

    As far as I am aware, FEP reporting cannot be done with that kind of granularity.

    You can run a report based on Add Remove Programs, I dont think this will suit your needs though.

    You can run reports on specific files, however, you have to specify that this information be collected before you can report on it - it's not overly efficient, and probably not that effective.

    Ask the people over in the Forefront Technet Forums perhaps?

    Cheers.


    LinkedIn

    Sunday, February 19, 2012 11:19 PM
  • Hi Arnel,

    As far as I am aware, FEP reporting cannot be done with that kind of granularity.

    You can run a report based on Add Remove Programs, I dont think this will suit your needs though.

    You can run reports on specific files, however, you have to specify that this information be collected before you can report on it - it's not overly efficient, and probably not that effective.

    Ask the people over in the Forefront Technet Forums perhaps?

    Cheers.


    LinkedIn

    Dear Adrian,

    This is what I also think at first, it took me 4 hours just for searching about this special procedure but to no avail, your right "granularity" is the right term for this settings which unfortunately maybe the Microsoft Forefront team will .

    To add up to my scenario, I can pinpoint infected machines through FEP report, now to segragate this computers to a collection through Querying (to save me time adding machines manually) and apply necessary patches is the whole idea.

    Thank you for understanding my requirement, although a "long shot" (Another set of procedure required) that might possible work is also suggested by Eswar. 

    Also thank you for Mr. Eswar regarding the suggestion. 

     

    Monday, February 20, 2012 5:16 AM
  • Arnel-101 I would avoid running SCCM as your malware scanner.  Get Forefront/MSE and let a proper tool do the work.

    This is an example of a report written by a junior I know, it works, but don't count on your server doing much while it's generating it.  It will cripple your reporting server and more than likely push all the other reports batched out of the way.  So word to the wise.  Use at your own risk.

    SELECT DISTINCT   RSYS.Name0 As 'Computer',    BI.SerialNumber0 As 'Serial Number',   RSYS.User_Name0 As 'Last User ID',    SF.FileName As 'File Name',    SF.FileDescription As 'File Description',    SF.FilePath As 'File Path',    SF.FileSize As 'File Size',    SF.FileVersion As 'File Version' FROM    V_R_SYSTEM RSYS    INNER JOIN V_GS_SoftwareFile SF ON RSYS.ResourceID = SF.ResourceID   INNER JOIN dbo.v_GS_PC_BIOS BI ON RSYS.ResourceID = BI.ResourceID     AND SF.FilePath  not like '%Research In Motion%' /*RIM Software*/     AND SF.FilePath  not like '%\VMware%' /* VMware Network sniffer*/     AND SF.FilePath  not like 'C:\Program Files\Hummingbird%' /* Hummingbird tool to configure the NIS */AND (  SF.FileDescription like '%doom%' OR /* DOOM Game */            SF.FileDescription like '%GNUTE%' OR  /*  MP3 Resources  */            SF.FileDescription like '%l0pht%'OR   /* Password cracker  */            SF.FileDescription like '%keylog%'OR   /* Key logger  */            SF.FileDescription like 'Lime%' OR   /* Peer-to-Peer file sharing  */            SF.FileDescription like '%nuke%' OR  /* DOOM Game  */            SF.FileDescription like '%orafice%' OR /* Keystroke mapper  */            SF.FileDescription like '%sniff%' OR /* Network sniffer  */            SF.FileDescription like '%unreal%' OR /* Games  */            SF.FileName like '%as-101%' OR             SF.FileName like '%babylon%' OR             --SF.FileName like '%bindin%' OR              SF.FileName like '%azure%' OR /* Torrent Client */            SF.FileName like '%bitt%' OR /* Torrent Client */            SF.FileName like '%bo2k%' OR             SF.FileName like '%BitComet%' OR /* Torrent Client */            SF.FileName like '%chknull%' OR             SF.FileName like '%Crack%' OR /* Password cracker  */            SF.FileName like '%Cracker%' OR /* Password cracker  */            SF.FileName like '%Craserv%' OR             SF.FileName like '%desktop.exe' OR            SF.FileName like '%doom%' OR /* DOOM game  */            SF.FileName like '%EbatesMoeMoney%' OR /* Spyware  */            SF.FileName like '%expolit%' OR            SF.FileName like '%exploit%' OR             SF.FileName like '%FROST%' OR   /* Peer-to-Peer file sharing  */            SF.FileName like 'gator%' OR   /* Gator Spyware/Adware  */            SF.FileName like '%getadmin%' OR             SF.FileName like '%GROK%' OR             SF.FileName like '%hack%' OR /* Password cracker  */            SF.FileName like '%hotbar%' OR /* IE Toolbar - Spyware/Adware  */            SF.FileName like '%kazaa%' OR   /*  Peer-to-Peer file sharing  */            SF.FileName like 'keylog%'OR  /* Password cracker  */            SF.FileName like 'keygen%'OR  /* Password cracker  */            SF.FileName like '%l0phtcrack%' OR /* Password cracker  */            SF.FileName like '%lc252install%' OR   /* Password cracker  */            SF.FileName like '%LIME%' OR   /* Peer-to-Peer file sharing  */            SF.FileName like '%GAMES%' OR /* Games file */            SF.FileName like '%morpheus%' OR             SF.FileName like '%Napster%' OR   /* Peer-to-Peer file sharing - MP3 Resources  */            SF.FileName like '%nbsvr%' OR             SF.FileName like '%nbtscan%' OR             SF.FileName like '%ndssnoop%' OR             SF.FileName like '%netbusr%' OR             SF.FileName like '%nmapNT%' OR             SF.FileName like '%nuke%' OR   /* DOOM Game  */            SF.FileName like '%nwpcrack%' OR             SF.FileName like '%orafice%' OR /* Keaystroke mapper  */            SF.FileName like '%otglove%' OR             SF.FileName like '%pwdump%' OR  /* Password cracker      */            SF.FileName like '%quake%' OR /*  DOOM game  */            SF.FileName like '%Retina%' OR             SF.FileName like '%RFPoison%' OR             SF.FileName like '%smbdie%' OR             SF.FileName like '%smurf%' OR             SF.FileName like '%torrent%' OR /* Torrent Client */            SF.FileName like '%unreal%' OR             SF.FileName like '%XUPITER%' OR             SF.FileName like 'POPSRV%' OR            SF.FileName like '%VUZE%' OR /* Torrent Client */            --SF.FilePath like '%My Shared folders' OR /* Shared Folders for P2P */            SF.FileName IN   ('_DLL.exe', /* Troj_Bagle.AC Trojan  */                              'ARR.exe',  /* Dial-up Hijacker - high cost toll number  */                              'asart.exe', /* ?  */                              'av.exe',   /* W32.Alphx.Word.A Virus  */                              'BackWeb.exe',  /* Spyware - BackWeb Technologies  */                              'Bargains.exe',  /* BargainBuddy - Adware/Spyware  */                              'BELT.exe',   /* Spyware - SearchV.com  */                              'Bling.exe',  /*  W32.SDBot-OH.Worm  */                              'BLSS.exe',  /* Spyware - CBlaster Trojan  */                              'Bootconf.exe',  /* Sypware - Homepage Hijacker  */                              'BonziBdy.exe',  /* Spyware  */                              'botzor.exe',  /* W32.ZOTOB.Worm  */                              'BPC.exe',  /* Spyware - Grokster  */                              'Bundle.exe',  /* Adware.SAHAgent  */                              'businessbg0002.exe',  /* Spyware - ?  */                              'cmesys.exe',  /* Adware.W32.Claria  */                              'crafty.exe',   /* ?  */                              'CFD.exe',  /* Spyware - Motive Cleint Foudation  */                              'csm.exe',  /* W32.ZOTOB.B Worm  */                              'Datemanager.exe',  /* Pop-Ups via Gator  */                              'DIVX.exe',  /* MASTAK Virus or NALDEM Trojan  */                              'DPPS2.exe',  /* Don't Panic! Pop-up blocker - Spyware  */                              'DSSagent.exe',  /* Adware - Broderbund - Spyware?  */                              'eanthology.exe',   /* eAcceleration Software Station - Spyware?  */                              'EditSRV.exe',  /* Spyware - Email_Update.exe  */                              'email_Update.exe',  /* StopSign Email Scanner - eAcceleration Software - Spyware?  */                              'EMSW.exe',  /* Spyware - Alset Inc.  */                              'Gator.exe',  /* Adware.W32.Claria  */                              'gmt.exe',  /* Adware.W32.Claria  */                              'haha.exe',  /* Myet Trojan  */                              'Hbinst.exe',  /* Spyware - HotBar  */                              'HBSRV.exe',  /* Spyware - HotBar  */                              'Hotbar.exe',  /* Spyware - HotBar  */                              'HXDL.exe',  /* HXDL Spyware - Gator  */                              'HXIUL.exe',  /* Adware - HelpExpress - Alset Inc.  */                              'IDHost.exe',  /* Topicks Spyware  */                              'IEDll.exe',  /* Homepage Hijacker  */                              'IEDriver.exe', /* Peer-To-Peer File Sharing  */                              'INFUS.exe',  /* Dial-up Hijacker - high cost toll number  */                              'InfWin.exe',  /* MSView Parasite  */                              'INTDEL.exe',  /* Adware - Pop-ups  */                              'ISTSVC.exe',  /* Spyware - Integrated Search Technologies  */                              'KeenValue.exe',  /* Spyware - Gator  */                              'loader.exe',   /* Backdoor.Prorat Virus  */                              'lol.exe',  /* W32.HLLW.Rackus Virus  */                              'Lspmonitor.exe', /* Spyware - StopSign  */                              'mapisvc32.exe',   /* KX Virus  */                              'MD.exe',  /* System MD Virus  */                              'MDie.exe',  /* Backdoor.Win32.Rbot.Gen Virus  */                              'MemoryMeter.exe',   /* Grokster Peer-To-Peer File Sharing Suite  */                              'MFIN32.exe',  /* Adware - MyFreeInternet Update  */                              'MMod.exe',  /* Adware.W32.EarnBundleWare  */                              'MOStat.exe',  /* Spyware - Wurld Media  */                              'mousebm.exe',  /* W32.ESBot Virus  */                              'mousemm.exe',  /* W32.ESBot.A Virus  */                              'MSBB.exe',   /* Adware.W32.BargainBuddy - 180Solutions  */                              'MSCache.exe',  /* Spyware - Integrated Search Technologies  */                              'MSCMan.exe',  /* Spyware - Odysseus Marketing  */                              'msdefr.exe',  /* Spybot Worm  */                              'MSMACROPROTXZ.exe',  /* Spybot Worm  */                              'MSMGT.exe',   /* Spyware - Total Velocity  */                              'MSSVR.exe',  /* Spyware - 2020DownLoader - 2020 Internet Search Toolbar  */                              'MSUpdater.exe',   /* TrojanDownLoader.Win32.WinShow Trojan  */                              'MWSOEMON.exe',  /* MyWebSearch Toolbar  */                              'mwsvm.exe',   /* Adware - Adw.ScanPortAL.A  */                              'Nail.exe',  /* Trojan.Win32.Stervis.B Trojan  */                              'nb32ext2.exe',  /* MyDoom.BV worm  */                              'nbmanager.exe',   /* Spyware - eAnthology  */                              'netbutler.exe',   /* ?  */                              'onsrvr.exe',  /* Spyware - OnWebMedia  */                              'PC32.exe',  /*  Mastak Virus  */                              'per.exe',  /* Worm.ZOTOB.C Virus  */                              'PGMonitr.exe',  /* Adware.W32.DelFin  */                              'PowerScan.exe',  /* Adware.W32.PowerScan  */                              'PRMVR.exe',  /* Spyware - Adtomi.com  */                              'pnpsrv.exe',   /* W32.SDBOT.Worm Virus  */                              'Precisiontime.exe',  /* Adware.W32.ClariaPrecision  */                              'PrizeSurfer.exe',/* Spyware - PrizeSurfer  */                              'Prmt.exe',  /* Spyware - OpiStat  */                              'RAY.exe',  /* Homepage Hijacker  */                              'RB32.exe',  /*  Adware.W32.RapicBlaster  */                              'RCSync.exe',  /*  Spyware - PrizeSurfer  */                              'Run32DLL.exe',  /* Key Recorder - Screen Capture - PAL PC Spy  */                              'SAHAgent.exe',  /* Adware.W32.CyDoor - CyDoor Desktop Media  */                              'savenow.exe',  /* Coupons - WhenU.com  */                              'SBHC.exe',   /* IE Plugin - GIGATech Software  */                              'ShowBehind.exe',  /* Adware - MicroSmarts Enterprise  */                              'SLMSS.exe',   /* Spyware - 2nd Thourgh by CPM Media  */                              'SRNG.exe',  /* Spyware - Search Hijacker  */                              'STCLoader.exe',   /*  Spyware - 2nd Thourgh by CPM Media  */                              'SUSP.exe',  /* Spyware - ABetterInternet  */                              'SVCINIT.exe',   /* Backdoor.Sinit Trojan  */                              'svnlitup32.exe',  /* Worm.RBOT.CBJ  */                              'syscpy.exe',   /* Backdoor.Hogle Trojan  */                              'Systesm32.exe',  /* Spyware - Bling.exe  */                              'thefourthcoming.exe',  /* ?  */                              'Trickler.exe',  /* Spyware - Gator GAIN (Gator Advertising and Info Network)  */                              'TSADBot.exe',  /* Adware  */                              'TVMD.exe',   /* Spyware  */                              'TVTMD.exe',  /* Spyware  */                              'UCMWESKU.exe', /* ?  */                              'Updates32.exe',  /* Spyware - Bling.exe  */                              'uptodate.exe',  /* Adware - BrowserPal  */                              'veloz.exe',   /* StopSign Email Scanner - eAcceleration Software  */                              'velozsys.exe',   /* StopSign Email Scanner - eAcceleration Software  */                              'Weather.exe',  /* Adware  */                              'webcel.exe',   /* eAcceleration Software - Spyware - ?  */                              'WebDev.exe',  /* ?  */                              'Win32US.exe',  /* Dial-up Hijacker - high cost toll number  */                              'WinActive.exe',  /* Homepage Hijacker  */                              'windrg32.exe',  /* W32.ZOTOB.D Worm  */                              'WinMain.exe',  /* Trojan.KonDeli  */                              'WinNet.exe',  /*  Adware/Spyware - CommonName I.E. Search  */                              'winpnp.exe',  /* W32.SDBOT.Worm  */                              'WinServN.exe',  /* Adware.W32.PurityScan - ClickSpring LLC  */                              'WinStart.exe',  /* Homepage Hijacker - iGetNet  */                              'WinStart001.exe',  /* Adware  */                              'wintbp.exe',  /* W32.ZOTOB.E Worm  */                              'wintbpx.exe',  /*  W32.BOZORI.Worm.B  */                              'WNAD.exe',  /* Spyware - TwistedHumor.com  */                              'wpa.exe',  /* ESBOT Worm  */                              'ygpmrgsb.exe',  /* ?  */                              'zeus.exe',   /* Zeus:Master of Olympus game  */                              'zmanager.exe'  /* Spyware - eAcceleration  */                              )              )ORDER BY    --RSYS.Name0    RSYS.User_Name0,    SF.FileName



    Friday, April 20, 2012 12:46 PM
  • Hi there

    The FEP Reporting Database is not the same database as the SCCM one.  For this reason I would think it extremely unlikely that you'd be able to build a collection of systems showing a particular infection.

    It's a good request though and I think you should raise that as a DCR with Microsoft.

    I don't have a console in front of me but I *think there is a collection of infected systems.  You could deploy to that and over-hit

    Friday, April 20, 2012 12:56 PM