I wanted to take a moment to share another couple of smart label SQL queries I put together for tracking software we don't want on our network. These two are fairly short, compared to the massive malware query I created. They do, nonetheless, catch some things you probably don't want on your network.
These follow the same template as my other queries, and are designed to be easily edited and tweaked for your particular network. Some changes may be necessary to avoid false positives. I spent some time running across various sites to get as many names of common and popular software that met the definitions of VPNs, Proxies, and Torrent clients, so it should be fairly complete, but I might've missed some all the same. Also don't forget these are designed in monospace code editors like Notepad ++. So they look ugly in this forum and in KACE's browser-based editor.
Feedback is welcome! I'm also happy to troubleshoot any problems you have if you try to set up this smart label yourself. :)
VPNs and Proxies Query
/* ##################################################### */
/* # PURPOSE: Flags Software Inventory items with the # */
/* # VPNs/Proxies label for tracking and reporting. # */
/* ##################################################### */ /* ##### COMMENTS ##### */
/* Display and Vendor names are encased in single quotes. Percents are wildcards. First block is names, second is publishers, third is excludes.
Please keep new entries alphabetical first, then search function second.
Please verify changes for false positives & update changelog. Suggested parsing editor is something monospaced. This editor is trash. */ /* ##### CHANGELOG ##### */
/*
05.06.2014 Real Name <email>
* Added 2 new signatures.
05.09.2014 Real Name <email>
* Cleaned up the script a little for uniformity.
* Added proxy-related entries.
*/ /* ##### BEGIN QUERY ####### */
/* # Leave this part alone. # */
/* ########################## */ SELECT ID FROM SOFTWARE WHERE /* ########## START NAME INCLUDES ######### */
/* # These all need to be "OR" and "like" # */
/* # New signature = add another paren! # */
/* # Parens in groups of 10, lines of 30. # */
/* ############################################# */
(((((((((( (((((((((( ((((( SOFTWARE.DISPLAY_NAME like '%VPN%')
OR SOFTWARE.DISPLAY_NAME like '%Dante client%')
OR SOFTWARE.DISPLAY_NAME like '%Freecap%')
OR SOFTWARE.DISPLAY_NAME like '%Proxifier%')
OR SOFTWARE.DISPLAY_NAME like '%ProxyCap%')
OR SOFTWARE.DISPLAY_NAME like '%proxychains%')
OR SOFTWARE.DISPLAY_NAME like '%redsocks%')
OR SOFTWARE.DISPLAY_NAME like '%Sockscap%')
OR SOFTWARE.DISPLAY_NAME like '%super Socks5Cap%')
OR SOFTWARE.DISPLAY_NAME like '%torsocks%')
OR SOFTWARE.DISPLAY_NAME like '%tun2socks%')
OR SOFTWARE.DISPLAY_NAME like '%Polipo%')
OR SOFTWARE.DISPLAY_NAME like '%Privoxy%')
OR SOFTWARE.DISPLAY_NAME like '%socat%')
OR SOFTWARE.DISPLAY_NAME like '%netcat%')
OR SOFTWARE.DISPLAY_NAME like '%WideCap%') /* ########## START PUBLISHER INCLUDES ######### */
/* # These all need to be "OR" and "like" # */
/* # New signature = add another paren! # */
/* ############################################# */
OR SOFTWARE.PUBLISHER like '%Inferno Nettverk%')
OR SOFTWARE.PUBLISHER like '%Max Artemev%')
OR SOFTWARE.PUBLISHER like '%Initex Software%')
OR SOFTWARE.PUBLISHER like '%Proxy Labs%')
OR SOFTWARE.PUBLISHER like '%Leonid Evdokimov%')
OR SOFTWARE.PUBLISHER like '%Networktunnel%')
OR SOFTWARE.PUBLISHER like '%Robert Hogan%')
OR SOFTWARE.PUBLISHER like '%Ambroz Bizjak%')
OR SOFTWARE.PUBLISHER like '%Max Artemev%')
/* ############### START EXCLUDES ############## */
/* # These all need to be "AND" and "not like" # */
/* # New signature = add another paren! # */
/* ############################################# */ /* ##### END QUERY ####### */
P2P and Torrent Clients
/* ##################################################### */
/* # PURPOSE: Flags Software Inventory items with the # */
/* # P2P/Torrent Clients label for tracking/reporting. # */
/* ##################################################### */ /* ##### COMMENTS ##### */
/* Display and Vendor names are encased in single quotes. Percents are wildcards. First block is names, second is publishers, third is excludes.
Please keep new entries alphabetical first, then search function second.
Please verify changes for false positives & update changelog. Suggested parsing editor is something monospaced. This editor is trash. */ /* ##### CHANGELOG ##### */
/*
04.22.2014 Real Name <email>
* Created query. 04.24.2014 Real Name <email>
* Added Mipony signature.
* Fixed formatting for ease of reading.
* Added comment blocks & changelog. 05.09.2014 Real Name <email>
* Cleaned up query for uniformity.
*/ /* ##### BEGIN QUERY ####### */
/* # Leave this part alone. # */
/* ########################## */ SELECT ID FROM SOFTWARE WHERE /* ########## START NAME INCLUDES ######### */
/* # These all should be "OR" and "like" # */
/* # New signature = add another paren! # */
/* # Parens in groups of 10, lines of 30. # */
/* ############################################# */
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( (( SOFTWARE.DISPLAY_NAME like '%torrent%')
OR SOFTWARE.DISPLAY_NAME like '%Acquisition%')
OR SOFTWARE.DISPLAY_NAME like '%ANts P2P%')
OR SOFTWARE.DISPLAY_NAME like '%Ares Galaxy%')
OR SOFTWARE.DISPLAY_NAME like '%Azureus%')
OR SOFTWARE.DISPLAY_NAME like '%BearShare%')
OR SOFTWARE.DISPLAY_NAME like '%BitComet%')
OR SOFTWARE.DISPLAY_NAME like '%BitLet%')
OR SOFTWARE.DISPLAY_NAME like '%BitLord%')
OR SOFTWARE.DISPLAY_NAME like '%Bits on Wheels%')
OR SOFTWARE.DISPLAY_NAME like '%BitSpirit%')
OR SOFTWARE.DISPLAY_NAME like '%BitTornado%')
OR SOFTWARE.DISPLAY_NAME like '%BitTyrant%')
OR SOFTWARE.DISPLAY_NAME like '%broolz%')
OR SOFTWARE.DISPLAY_NAME like '%Cabos%')
OR SOFTWARE.DISPLAY_NAME like '%Calypso%')
OR SOFTWARE.DISPLAY_NAME like '%Datawire%')
OR SOFTWARE.DISPLAY_NAME like '%DC++%')
OR SOFTWARE.DISPLAY_NAME like '%Deluge%')
OR SOFTWARE.DISPLAY_NAME like '%eDonkey2000%')
OR SOFTWARE.DISPLAY_NAME like '%eMule%')
OR SOFTWARE.DISPLAY_NAME like '%ExoSee%')
OR SOFTWARE.DISPLAY_NAME like '%Filetopia%')
OR SOFTWARE.DISPLAY_NAME like '%Flashget%')
OR SOFTWARE.DISPLAY_NAME like '%Folx%')
OR SOFTWARE.DISPLAY_NAME like '%FProxy%')
OR SOFTWARE.DISPLAY_NAME like '%Free Download Manager%')
OR SOFTWARE.DISPLAY_NAME like '%Frost%')
OR SOFTWARE.DISPLAY_NAME like '%GetRight%')
OR SOFTWARE.DISPLAY_NAME like '%Gnucleus%')
OR SOFTWARE.DISPLAY_NAME like '%GNUnet%')
OR SOFTWARE.DISPLAY_NAME like '%gtk-gnutella%')
OR SOFTWARE.DISPLAY_NAME like '%I2Phex%')
OR SOFTWARE.DISPLAY_NAME like '%I2PSnark%')
OR SOFTWARE.DISPLAY_NAME like '%iMesh%')
OR SOFTWARE.DISPLAY_NAME like '%iMule%')
OR SOFTWARE.DISPLAY_NAME like '%Kazaa Lite%')
OR SOFTWARE.DISPLAY_NAME like '%Kazaa%')
OR SOFTWARE.DISPLAY_NAME like '%KCeasy%')
OR SOFTWARE.DISPLAY_NAME like '%KGet%')
OR SOFTWARE.DISPLAY_NAME like '%Lftp%')
OR SOFTWARE.DISPLAY_NAME like '%LimeWire%')
OR SOFTWARE.DISPLAY_NAME like '%Manolito%')
OR SOFTWARE.DISPLAY_NAME like '%Mipony%')
OR SOFTWARE.DISPLAY_NAME like '%Miro%')
OR SOFTWARE.DISPLAY_NAME like '%MLDonkey%')
OR SOFTWARE.DISPLAY_NAME like '%Morpheus%')
OR SOFTWARE.DISPLAY_NAME like '%MUTE%')
OR SOFTWARE.DISPLAY_NAME like '%Nachtblitz%')
OR SOFTWARE.DISPLAY_NAME like '%Net Transport%')
OR SOFTWARE.DISPLAY_NAME like '%Nodezilla%')
OR SOFTWARE.DISPLAY_NAME like '%OneSwarm%')
OR SOFTWARE.DISPLAY_NAME like '%Perfect Dark%')
OR SOFTWARE.DISPLAY_NAME like '%Piolet%')
OR SOFTWARE.DISPLAY_NAME like '%Retroshare%')
OR SOFTWARE.DISPLAY_NAME like '%Shareaza%')
OR SOFTWARE.DISPLAY_NAME like '%Sharing Max%')
OR SOFTWARE.DISPLAY_NAME like '%SoMud%')
OR SOFTWARE.DISPLAY_NAME like '%Soulseek%')
OR SOFTWARE.DISPLAY_NAME like '%StealthNet%')
OR SOFTWARE.DISPLAY_NAME like '%Thaw%')
OR SOFTWARE.DISPLAY_NAME like '%Transmission%')
OR SOFTWARE.DISPLAY_NAME like '%Tribler%')
OR SOFTWARE.DISPLAY_NAME like '%TrustyFiles%')
OR SOFTWARE.DISPLAY_NAME like '%uGet%')
OR SOFTWARE.DISPLAY_NAME like '%Vuze%')
OR SOFTWARE.DISPLAY_NAME like '%Warez P2P%')
OR SOFTWARE.DISPLAY_NAME like '%WinMX%')
OR SOFTWARE.DISPLAY_NAME like '%Winny%')
OR SOFTWARE.DISPLAY_NAME like '%Wuala%')
OR SOFTWARE.DISPLAY_NAME like '%Wyzo%')
OR SOFTWARE.DISPLAY_NAME like '%Xunlei%')
OR SOFTWARE.DISPLAY_NAME like '%YobiDrive FLOWS%')
OR SOFTWARE.DISPLAY_NAME like 'ABC%')
OR SOFTWARE.DISPLAY_NAME like 'aMule%')
OR SOFTWARE.DISPLAY_NAME like 'giFT%')
OR SOFTWARE.DISPLAY_NAME like 'Opera%')
OR SOFTWARE.DISPLAY_NAME like 'Robert%')
OR SOFTWARE.DISPLAY_NAME like 'RShare%')
OR SOFTWARE.DISPLAY_NAME = '%Share%') /* ############### START EXCLUDES ############## */
/* # These all need to be "AND" and "not like" # */
/* # New signature = add another paren! # */
/* ############################################# */
AND SOFTWARE.DISPLAY_NAME not like 'ABC World')
AND SOFTWARE.DISPLAY_NAME not like 'Operations and Algebraic Thinking Mole in the Hole Interactive Game') /* ##### END QUERY ####### */
I am getting the following error.
Error Running Report
The query does not contain specified break field. - Blackbear99 9 years ago