/build/static/layout/Breadcrumb_cap_w.png

Blog Posts tagged with Smart Labels

Ask a question

Massive Malware Smart Label (and K1000 Scripting Practices)

(AUTHOR'S NOTE: Check out my other two queries for VPNs/Proxies and P2P/Torrent Clients over here! Also, you might want to follow this post, as I'll update the query as I change it and find more signatures to track!)

So a few months ago I was tasked with tracking down some Conduit malware infections in my enterprise setting. I was provided with this nice little print out of IP addresses and was told to track them down manually and fix the problem. I knew I could do a more efficient job by using KACE inventory tracking and reports.

Since I was assigned that little malware-cleanup job, I've hand-filtered through over 16,750 software signatures gathered from over 9000 workstations in our enterprise. I wanted to share this massive smart-label script I created. I also think it's a good example of how to produce a well-documented script that is easily understood by newcomers. Not that KACE SQL is that complicated... But still!

The query catches all malware names that I could find based on Vendor and Display fields. This pastes right into the smart label script - you can use the wizard to create one and paste this in there. Everything is commented to hell and back, so it largely explains itself. Remember when creating scripts you should comment everything so that new people coming in can make sense of what you've written. Changelogs, while bloating the line count, are useful for troubleshooting if something goes wrong. Since KACE editor is not monospaced font the layout gets a little funky. I chose to keep it functional for monospaced editors because I do all my editing in Notepad++.

This script catches approximately 270 different software names and publishers, with about 10 exludes built in to avoid common false-positives I ran across, and a switch to only put softwares in the Malware label if they aren't rated with Threat 5. Essentially what this does is creates a nice little label that only shows up in my list if NEW malware comes onto my network, and anything I've already identified and flagged Threat 5 is ignored. So any time I see the Malware label in my list, I know there's new malware I need to categorize. If you want the label to stick all the time, you can just comment out or remove the last line.

The intention here is to use the Reporting features to generate a report that shows machines with Threat 5 software (see link below for example report). You can design the report with the wizard so that it shows machines by IP and even username logged in, so you can see exactly who and where the infections are. If your enterprise uses VNC or something similar, you can easily track users down and clean up the infection.

You can change little things here and there. Most of my signatures will catch the words between the parens if they show up ANYWHERE in that field. That's why, for example, I commented out "Converter" because there were lots of legit files with the word converter in them. If you know a file started with the word Converter, you could remove the first % so it read "... like 'Converter%')" for example.

Below I've linked an image gallery to show how I used the KACE Report Wizard to set up the report I use in conjunction with the Smart Label query I've pasted into the code box below that. Just keep in mind that Report won't show anything until you go into Software Inventory, use "View All" to view the Malware label, and classify it all as Threat 5, since the report operates off the Threat rating, and not the Malware label itself. Enjoy! :)

Report Wizard Gallery here: (link outdated with updated KACE release... Sorry folks, don't have time to fix it!)

/* ##################################################### */
/* # PURPOSE: Flags Software Inventory items with the # */
/* # Malware label for quick flagging 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 ##### */
/*
04.22.2014 Real Name <email>
* Created query.

04.23.2014 Real Name <email>
* Added 100+ more signatures.

04.24.2014 Real Name <email>
* Added 100+ more signatures.
* Fixed formatting for ease of reading.
* Added comment blocks & changelog. 05.05.2014 Real Name <email>
* Change 'File Type Assistant' to 'File Type' for broader catch.
* Added 2 new signatures.
* Removed filter for 'IOBit' signature.
* Moved commented lines and added "Disabled Entries" section. 05.06.2014 Real Name <email>
* Added 2 new signatures. 05.07.2014 Real Name <email>
* Added 11 new signatures.
* Removed 1 signature. 05.08.2014 Real Name <email>
* Added 3 new signatures.

05.08.2014 Real Name <email>
* Cleaned up the script a little for uniformity.
*/ /* ##### 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. # */
/* ############################################# */
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( (((((((((( ((((((((((
(((((((((( ( /* # DISABLED ENTRIES # */
/* ##################### */
/*((
OR SOFTWARE.DISPLAY_NAME like '%Convert%')
OR SOFTWARE.DISPLAY_NAME like '%Microsoft Search Enhancement Pack%') */
/* ##################### */ SOFTWARE.DISPLAY_NAME like '%24x7 Help%')
OR SOFTWARE.DISPLAY_NAME like '%advanced registry optimizer%')
OR SOFTWARE.DISPLAY_NAME like '%Advanced System Protector%')
OR SOFTWARE.DISPLAY_NAME like '%Allyrics-22%')
OR SOFTWARE.DISPLAY_NAME like '%appbar%')
OR SOFTWARE.DISPLAY_NAME like '%appgraffiti%')
OR SOFTWARE.DISPLAY_NAME like '%Babylon%')
OR SOFTWARE.DISPLAY_NAME like '%backupdutylite%')
OR SOFTWARE.DISPLAY_NAME like '%BitGuard%')
OR SOFTWARE.DISPLAY_NAME like '%Blitz Media Player%')
OR SOFTWARE.DISPLAY_NAME like '%Browse For Change%')
OR SOFTWARE.DISPLAY_NAME like '%BrowserProtect%')
OR SOFTWARE.DISPLAY_NAME like '%browsersafeguard%')
OR SOFTWARE.DISPLAY_NAME like '%browsetosave%')
OR SOFTWARE.DISPLAY_NAME like '%Buzz-it%')
OR SOFTWARE.DISPLAY_NAME like '%BuzzSearch%')
OR SOFTWARE.DISPLAY_NAME like '%cioolsalecooupon%')
OR SOFTWARE.DISPLAY_NAME like '%clean water action%')
OR SOFTWARE.DISPLAY_NAME like '%Community Smartbar%')
OR SOFTWARE.DISPLAY_NAME like '%Conduit%')
OR SOFTWARE.DISPLAY_NAME like '%consumer input%')
OR SOFTWARE.DISPLAY_NAME like '%ConvertHelper%')
OR SOFTWARE.DISPLAY_NAME like '%Coupon%')
OR SOFTWARE.DISPLAY_NAME like '%Crawler%')
OR SOFTWARE.DISPLAY_NAME like '%crossreader%')
OR SOFTWARE.DISPLAY_NAME like '%Deal Boat%')
OR SOFTWARE.DISPLAY_NAME like '%Dealio%')
OR SOFTWARE.DISPLAY_NAME like '%DealPly%')
OR SOFTWARE.DISPLAY_NAME like '%Deals%')
OR SOFTWARE.DISPLAY_NAME like '%DefaultTab%')
OR SOFTWARE.DISPLAY_NAME like '%Delta%')
OR SOFTWARE.DISPLAY_NAME like '%Dictionaryboss%')
OR SOFTWARE.DISPLAY_NAME like '%Dmuninstaller%')
OR SOFTWARE.DISPLAY_NAME like '%Driver Performer%')
OR SOFTWARE.DISPLAY_NAME like '%driverupdate%')
OR SOFTWARE.DISPLAY_NAME like '%facemoods%')
OR SOFTWARE.DISPLAY_NAME like '%Fast Free Converter%')
OR SOFTWARE.DISPLAY_NAME like '%Fast Search%')
OR SOFTWARE.DISPLAY_NAME like '%File Type%')
OR SOFTWARE.DISPLAY_NAME like '%Files Opened%')
OR SOFTWARE.DISPLAY_NAME like '%free file viewer%')
OR SOFTWARE.DISPLAY_NAME like '%free opener%')
OR SOFTWARE.DISPLAY_NAME like '%Free Video Player%')
OR SOFTWARE.DISPLAY_NAME like '%freemake%')
OR SOFTWARE.DISPLAY_NAME like '%Funmoods%')
OR SOFTWARE.DISPLAY_NAME like '%Gaming Extension%')
OR SOFTWARE.DISPLAY_NAME like '%genieo%')
OR SOFTWARE.DISPLAY_NAME like '%genieoExtra%')
OR SOFTWARE.DISPLAY_NAME like '%highlightly%')
OR SOFTWARE.DISPLAY_NAME like '%Hoopla%')
OR SOFTWARE.DISPLAY_NAME like '%I Want This%')
OR SOFTWARE.DISPLAY_NAME like '%IB Updater%')
OR SOFTWARE.DISPLAY_NAME like '%iLivid%')
OR SOFTWARE.DISPLAY_NAME like '%IM completer%')
OR SOFTWARE.DISPLAY_NAME like '%image converter%')
OR SOFTWARE.DISPLAY_NAME like '%Iminent%')
OR SOFTWARE.DISPLAY_NAME like '%InboxAce%')
OR SOFTWARE.DISPLAY_NAME like '%Incredibar%')
OR SOFTWARE.DISPLAY_NAME like '%installconverter%')
OR SOFTWARE.DISPLAY_NAME like '%installmac%')
OR SOFTWARE.DISPLAY_NAME like '%InstallX Search Protect%')
OR SOFTWARE.DISPLAY_NAME like '%Internet Turbo%')
OR SOFTWARE.DISPLAY_NAME like '%InternetHelper%')
OR SOFTWARE.DISPLAY_NAME like '%Iwebar%')
OR SOFTWARE.DISPLAY_NAME like '%level quality%')
OR SOFTWARE.DISPLAY_NAME like '%Linksicle%')
OR SOFTWARE.DISPLAY_NAME like '%Lpt System Updater%')
OR SOFTWARE.DISPLAY_NAME like '%LTCM Client %')
OR SOFTWARE.DISPLAY_NAME like '%Lyri%')
OR SOFTWARE.DISPLAY_NAME like '%Mega Browse%')
OR SOFTWARE.DISPLAY_NAME like '%MixiDJ%')
OR SOFTWARE.DISPLAY_NAME like '%mobogenie%')
OR SOFTWARE.DISPLAY_NAME like '%mplayer%')
OR SOFTWARE.DISPLAY_NAME like '%muvic%')
OR SOFTWARE.DISPLAY_NAME like '%My Scrap Nook%')
OR SOFTWARE.DISPLAY_NAME like '%My Web Search%')
OR SOFTWARE.DISPLAY_NAME like '%MyPC Backup%')
OR SOFTWARE.DISPLAY_NAME like '%Mysearchdial%')
OR SOFTWARE.DISPLAY_NAME like '%NetAssistant%')
OR SOFTWARE.DISPLAY_NAME like '%Netzero%')
OR SOFTWARE.DISPLAY_NAME like '%Online Vault%')
OR SOFTWARE.DISPLAY_NAME like '%Open It!%')
OR SOFTWARE.DISPLAY_NAME like '%openfreely%')
OR SOFTWARE.DISPLAY_NAME like '%Optimizer Pro%')
OR SOFTWARE.DISPLAY_NAME like '%ParetoLogic%')
OR SOFTWARE.DISPLAY_NAME like '%pc clean%')
OR SOFTWARE.DISPLAY_NAME like '%pc health%')
OR SOFTWARE.DISPLAY_NAME like '%PC Optimizer%')
OR SOFTWARE.DISPLAY_NAME like '%PC Performer%')
OR SOFTWARE.DISPLAY_NAME like '%playbryte%')
OR SOFTWARE.DISPLAY_NAME like '%Plus-hd%')
OR SOFTWARE.DISPLAY_NAME like '%PriceGong%')
OR SOFTWARE.DISPLAY_NAME like '%PricePeep%')
OR SOFTWARE.DISPLAY_NAME like '%privacy safeguard%')
OR SOFTWARE.DISPLAY_NAME like '%quiknowledge%')
OR SOFTWARE.DISPLAY_NAME like '%qwiklinx%')
OR SOFTWARE.DISPLAY_NAME like '%regcure%')
OR SOFTWARE.DISPLAY_NAME like '%RegCurePro%')
OR SOFTWARE.DISPLAY_NAME like '%regcurePro%')
OR SOFTWARE.DISPLAY_NAME like '%registry dr%')
OR SOFTWARE.DISPLAY_NAME like '%registrydr%')
OR SOFTWARE.DISPLAY_NAME like '%regwork%')
OR SOFTWARE.DISPLAY_NAME like '%re-markit%')
OR SOFTWARE.DISPLAY_NAME like '%Savekeep%')
OR SOFTWARE.DISPLAY_NAME like '%savesense%')
OR SOFTWARE.DISPLAY_NAME like '%SaveValet%')
OR SOFTWARE.DISPLAY_NAME like '%Savings%')
OR SOFTWARE.DISPLAY_NAME like '%Search module%')
OR SOFTWARE.DISPLAY_NAME like '%Search Protect%')
OR SOFTWARE.DISPLAY_NAME like '%Search Settings%')
OR SOFTWARE.DISPLAY_NAME like '%searchassist%')
OR SOFTWARE.DISPLAY_NAME like '%Searchqu%')
OR SOFTWARE.DISPLAY_NAME like '%SearchYa%')
OR SOFTWARE.DISPLAY_NAME like '%Selectionlinks%')
OR SOFTWARE.DISPLAY_NAME like '%Shop To Win%')
OR SOFTWARE.DISPLAY_NAME like '%Shopop%')
OR SOFTWARE.DISPLAY_NAME like '%Shopper%')
OR SOFTWARE.DISPLAY_NAME like '%shopping%')
OR SOFTWARE.DISPLAY_NAME like '%siteranker%')
OR SOFTWARE.DISPLAY_NAME like '%smartbar%')
OR SOFTWARE.DISPLAY_NAME like '%snap.do%')
OR SOFTWARE.DISPLAY_NAME like '%Softsafe%')
OR SOFTWARE.DISPLAY_NAME like '%software version updater%')
OR SOFTWARE.DISPLAY_NAME like '%speed%')
OR SOFTWARE.DISPLAY_NAME like '%speedypc%')
OR SOFTWARE.DISPLAY_NAME like '%strongvault%')
OR SOFTWARE.DISPLAY_NAME like '%surf%')
OR SOFTWARE.DISPLAY_NAME like '%swag%')
OR SOFTWARE.DISPLAY_NAME like '%swagbucks%')
OR SOFTWARE.DISPLAY_NAME like '%television%')
OR SOFTWARE.DISPLAY_NAME like '%The Sea App %')
OR SOFTWARE.DISPLAY_NAME like '%tube dimmer%')
OR SOFTWARE.DISPLAY_NAME like '%tuneupmymac%')
OR SOFTWARE.DISPLAY_NAME like '%uninstall helper%')
OR SOFTWARE.DISPLAY_NAME like '%url assistant%')
OR SOFTWARE.DISPLAY_NAME like '%VO Package%')
OR SOFTWARE.DISPLAY_NAME like '%video player%')
OR SOFTWARE.DISPLAY_NAME like '%videoconverter%')
OR SOFTWARE.DISPLAY_NAME like '%videoplayer%')
OR SOFTWARE.DISPLAY_NAME like '%visualbee%')
OR SOFTWARE.DISPLAY_NAME like '%w3i%')
OR SOFTWARE.DISPLAY_NAME like '%wajam%')
OR SOFTWARE.DISPLAY_NAME like '%weather channel%')
OR SOFTWARE.DISPLAY_NAME like '%weatherbug%')
OR SOFTWARE.DISPLAY_NAME like '%web assistant%')
OR SOFTWARE.DISPLAY_NAME like '%web layers%')
OR SOFTWARE.DISPLAY_NAME like '%web protect%')
OR SOFTWARE.DISPLAY_NAME like '%Web-cake%')
OR SOFTWARE.DISPLAY_NAME like '%webcake%')
OR SOFTWARE.DISPLAY_NAME like '%websteroids%')
OR SOFTWARE.DISPLAY_NAME like '%wildtangent%')
OR SOFTWARE.DISPLAY_NAME like '%yontoo%')
OR SOFTWARE.DISPLAY_NAME like '%youtube downloader%')
OR SOFTWARE.DISPLAY_NAME like '%ytd%')
OR SOFTWARE.DISPLAY_NAME like 'saver%')
OR SOFTWARE.DISPLAY_NAME like 'Shop%') /* ########## START PUBLISHER INCLUDES ######### */
/* # These all need to be "OR" and "like" # */
/* # New signature = add another paren! # */
/* ############################################# */
OR SOFTWARE.PUBLISHER like '%215 apps%')
OR SOFTWARE.PUBLISHER like '%adpeak%')
OR SOFTWARE.PUBLISHER like '%Alactro%')
OR SOFTWARE.PUBLISHER like '%ALOT%')
OR SOFTWARE.PUBLISHER like '%apn%')
OR SOFTWARE.PUBLISHER like '%aws convergence%')
OR SOFTWARE.PUBLISHER like '%backupdutylite%')
OR SOFTWARE.PUBLISHER like '%Bandoo%')
OR SOFTWARE.PUBLISHER like '%betwikx%')
OR SOFTWARE.PUBLISHER like '%bitberry%')
OR SOFTWARE.PUBLISHER like '%blue labs%')
OR SOFTWARE.PUBLISHER like '%browsersafeguard%')
OR SOFTWARE.PUBLISHER like '%compete%')
OR SOFTWARE.PUBLISHER like '%compuclever%')
OR SOFTWARE.PUBLISHER like '%Conduit%')
OR SOFTWARE.PUBLISHER like '%creative island media%')
OR SOFTWARE.PUBLISHER like '%crossreader%')
OR SOFTWARE.PUBLISHER like '%dealply%')
OR SOFTWARE.PUBLISHER like '%delta%')
OR SOFTWARE.PUBLISHER like '%DomaIQ%')
OR SOFTWARE.PUBLISHER like '%download freely%')
OR SOFTWARE.PUBLISHER like '%DownloadHelper%')
OR SOFTWARE.PUBLISHER like '%Ellora%')
OR SOFTWARE.PUBLISHER like '%exent%')
OR SOFTWARE.PUBLISHER like '%ez freeware%')
OR SOFTWARE.PUBLISHER like '%facemoods%')
OR SOFTWARE.PUBLISHER like '%fast free converter%')
OR SOFTWARE.PUBLISHER like '%freeze.com%')
OR SOFTWARE.PUBLISHER like '%funmoods%')
OR SOFTWARE.PUBLISHER like '%gigaclicks%')
OR SOFTWARE.PUBLISHER like '%GreenTree%')
OR SOFTWARE.PUBLISHER like '%growth systems%')
OR SOFTWARE.PUBLISHER like '%highlightly%')
OR SOFTWARE.PUBLISHER like '%Honlyn Limited%')
OR SOFTWARE.PUBLISHER like '%ibrytre%')
OR SOFTWARE.PUBLISHER like '%image converter%')
OR SOFTWARE.PUBLISHER like '%iminent%')
OR SOFTWARE.PUBLISHER like '%incredibar%')
OR SOFTWARE.PUBLISHER like '%incredimail%')
OR SOFTWARE.PUBLISHER like '%innovative apps%')
OR SOFTWARE.PUBLISHER like '%installconverter%')
OR SOFTWARE.PUBLISHER like '%InstallX%')
OR SOFTWARE.PUBLISHER like '%internethelper%')
OR SOFTWARE.PUBLISHER like '%iwebar%')
OR SOFTWARE.PUBLISHER like '%jdi backup%')
OR SOFTWARE.PUBLISHER like '%jenkat media%')
OR SOFTWARE.PUBLISHER like '%level quality%')
OR SOFTWARE.PUBLISHER like '%linksicle%')
OR SOFTWARE.PUBLISHER like '%linkury%')
OR SOFTWARE.PUBLISHER like '%Lyri%')
OR SOFTWARE.PUBLISHER like '%mediatechsoft%')
OR SOFTWARE.PUBLISHER like '%Mindspark Interactive%')
OR SOFTWARE.PUBLISHER like '%mixidj%')
OR SOFTWARE.PUBLISHER like '%my pop%')
OR SOFTWARE.PUBLISHER like '%my scrap nook%')
OR SOFTWARE.PUBLISHER like '%my web search%')
OR SOFTWARE.PUBLISHER like '%mypc backup%')
OR SOFTWARE.PUBLISHER like '%mysearchdial%')
OR SOFTWARE.PUBLISHER like '%omega partners%')
OR SOFTWARE.PUBLISHER like '%ooo industry%')
OR SOFTWARE.PUBLISHER like '%openit%')
OR SOFTWARE.PUBLISHER like '%Paretologic%')
OR SOFTWARE.PUBLISHER like '%pc health%')
OR SOFTWARE.PUBLISHER like '%pc optimizer pro%')
OR SOFTWARE.PUBLISHER like '%pc utilities%')
OR SOFTWARE.PUBLISHER like '%pcrx.com%')
OR SOFTWARE.PUBLISHER like '%performersoft%')
OR SOFTWARE.PUBLISHER like '%pinwid%')
OR SOFTWARE.PUBLISHER like '%playbryte%')
OR SOFTWARE.PUBLISHER like '%plus hd%')
OR SOFTWARE.PUBLISHER like '%pricegong%')
OR SOFTWARE.PUBLISHER like '%privacy safeguard%')
OR SOFTWARE.PUBLISHER like '%quiknowledge%')
OR SOFTWARE.PUBLISHER like '%qwiklinx%')
OR SOFTWARE.PUBLISHER like '%regcure%')
OR SOFTWARE.PUBLISHER like '%re-markit%')
OR SOFTWARE.PUBLISHER like '%rightsurf%')
OR SOFTWARE.PUBLISHER like '%savings%')
OR SOFTWARE.PUBLISHER like '%search module%')
OR SOFTWARE.PUBLISHER like '%search results%')
OR SOFTWARE.PUBLISHER like '%selectionlinks%')
OR SOFTWARE.PUBLISHER like '%shop to win%')
OR SOFTWARE.PUBLISHER like '%shopperreports%')
OR SOFTWARE.PUBLISHER like '%shoppingchip%')
OR SOFTWARE.PUBLISHER like '%showpass%')
OR SOFTWARE.PUBLISHER like '%slimware%')
OR SOFTWARE.PUBLISHER like '%speedypc software%')
OR SOFTWARE.PUBLISHER like '%spigot%')
OR SOFTWARE.PUBLISHER like '%strongvault%')
OR SOFTWARE.PUBLISHER like '%suprasavings%')
OR SOFTWARE.PUBLISHER like '%surf canyon%')
OR SOFTWARE.PUBLISHER like '%suurfkeepit%')
OR SOFTWARE.PUBLISHER like '%sweetpacks%')
OR SOFTWARE.PUBLISHER like '%systemspeedup%')
OR SOFTWARE.PUBLISHER like '%systweak%')
OR SOFTWARE.PUBLISHER like '%television%')
OR SOFTWARE.PUBLISHER like '%tuguu%')
OR SOFTWARE.PUBLISHER like '%Uniblue systems%')
OR SOFTWARE.PUBLISHER like '%video player%')
OR SOFTWARE.PUBLISHER like '%visual tools%')
OR SOFTWARE.PUBLISHER like '%visualbee%')
OR SOFTWARE.PUBLISHER like '%volonet%')
OR SOFTWARE.PUBLISHER like '%w3i%')
OR SOFTWARE.PUBLISHER like '%wajam%')
OR SOFTWARE.PUBLISHER like '%wajam%')
OR SOFTWARE.PUBLISHER like '%We-care.com%')
OR SOFTWARE.PUBLISHER like '%web cake%')
OR SOFTWARE.PUBLISHER like '%web layers%')
OR SOFTWARE.PUBLISHER like '%web protect%')
OR SOFTWARE.PUBLISHER like '%webcake%')
OR SOFTWARE.PUBLISHER like '%wildtangent%')
OR SOFTWARE.PUBLISHER like '%xportsoft%')
OR SOFTWARE.PUBLISHER like '%yontoo%')
OR SOFTWARE.PUBLISHER like 'resoft%')
/* ############### START EXCLUDES ############## */
/* # These all need to be "AND" and "not like" # */
/* # New signature = add another paren! # */
/* ############################################# */
AND SOFTWARE.PUBLISHER not like '%Aimersoft%')
AND SOFTWARE.PUBLISHER not like '%DivX%')
AND SOFTWARE.DISPLAY_NAME not like '%canon%')
AND SOFTWARE.DISPLAY_NAME not like '%deltagraph%')
AND SOFTWARE.DISPLAY_NAME not like '%Keyspan High Speed USB Serial Adapter%')
AND SOFTWARE.DISPLAY_NAME not like '%MAGIX Speed burnR%')
AND SOFTWARE.DISPLAY_NAME not like '%panasonic%')
AND SOFTWARE.DISPLAY_NAME not like '%speedstudy%')
AND SOFTWARE.DISPLAY_NAME not like '%SpeedswitchXP%')
AND SOFTWARE.DISPLAY_NAME not like '%VPN%') /* ######################### The Label Switch ######################### */
/* # Comment this to ID ALL software listed above as malware. # */
/* # Uncomment this to only ID software that haven't been categorized. # */
/* ###################################################################### */
AND SOFTWARE.THREAT != '5') /* ##### END QUERY ####### */
View comments (10)

Smart Label Queries for VPNs/Proxies and P2P/Torrent Clients

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 ####### */


View comments (1)

Creating a K1000 Smart Label based on services that are running

When creating a smart label for machines, there is no option to select services as a criteria.  There have been posts that have done similar things based on custom inventory fields, but there is a way to generate a smart label based on whether a service is running.  This is of course only as good as the last inventory reported, but still very useful.

1.  Create a new smart label with any criteria.  We will replace the SQL query for the label.  Here we will look for MS Exchange Services.

K7Tpiy.png

2. Next we need to Identify the service you want to check for.  Is this case we will use the Display Name of the service, but we could use the process name as well with a slightly different query.  Go to the device -> services and find  your service.  

qWKxcL.png

3. Next, edit your SQL statement for the smart label you created in step 1.

bAwhe4.png


4. Paste in the following SQL statement. (use your service Display Name in place of red text below).

SELECT MACHINE.NAME,
       MACHINE.IP,
       NTSERVICE.ID,
       NTSERVICE.STATUS,
       NTSERVICE.DISPLAY_NAME,
       MACHINE.MAC,
       MACHINE.SYSTEM_DESCRIPTION
  FROM (ORG1.MACHINE_NTSERVICE_JT MACHINE_NTSERVICE_JT
        INNER JOIN ORG1.NTSERVICE NTSERVICE
           ON (MACHINE_NTSERVICE_JT.NTSERVICE_ID = NTSERVICE.ID))
       INNER JOIN ORG1.MACHINE MACHINE
          ON (MACHINE_NTSERVICE_JT.MACHINE_ID = MACHINE.ID)
 WHERE (    NTSERVICE.STATUS = 'SERVICE_RUNNING'
        AND NTSERVICE.DISPLAY_NAME = 'Microsoft Exchange Information Store')

5.  Save your Smart Label and have your machines check in to get the label applied.

Enjoy!
View comments (1)

Smart Label based on OS Installation Date

There may be times when you need to group devices by installation date.   A practical example might be that you want to deploy an image to a system that has not be re imaged in 2 years.  Another reason to use this might be if you are deploying images and you want to run an aggressive patch schedule on newly imaged systems.  Since we cannot rely solely on K1000 agent creation date as this would apply to new agent installs and not only new machines, we need to separate the machines that were just re imaged from those that received the agent recently.

The problem lies in the smart label drop downs.  Not every inventory field is shown when building a smart label and the inventory item OS Install Date is one of those items.  In order to build a smart label for this we can use a simple SQL query pasted in over a new smart label.

First, build a new smart label from devices.  It does not matter what you use since we will wipe out the query.  Go to Label Management --> Smart Labels and click on the name of the smart label that was created.

JG5TGv.png

Click on Edit SQL and paste in the following SQL statement:

SELECT MACHINE.NAME AS SYSTEM_NAME, OS_INSTALLED_DATE as TOPIC_ID FROM MACHINE  WHERE (((TIMESTAMP(MACHINE.OS_INSTALLED_DATE) <= NOW() AND TIMESTAMP(MACHINE.OS_INSTALLED_DATE) > DATE_SUB(NOW(),INTERVAL 48 HOUR))))

Save the label.  This will automatically label devices that were imaged within the last 48 hours.

Now we can use that label in a patch schedule that aggressively patches machines in that label while keeping older machines out of that patch schedule.

We can alter the time query a bit to find whatever devices we need.  For example devices that were imaged over 2 years ago would use this query:

SELECT MACHINE.NAME AS SYSTEM_NAME, OS_INSTALLED_DATE as TOPIC_ID FROM MACHINE  WHERE ((TIMESTAMP(MACHINE.OS_INSTALLED_DATE) < DATE_SUB(NOW(),INTERVAL 2 YEAR)))

Happy labeling!
View comments (4)

How-To: Unique Method to Apply a Label to Multiple Machines

NOTICE: I found a MUCH simpler way to do this here (thanks JasonEgg): http://www.itninja.com/question/how-do-i-populate-the-device-list-in-a-script-by-importing-contents-of-a-csv-file

Basically, just create a regular Smart Label in the Web GUI, and use System Name matches regex then the machine names separated by a pipe.

Example:



If you have a list separated by a new line or a csv of names, then you can use Notepad++ with the below Find/Replace to convert the list to the Regex query needed:


Original blog below:

------------------------------------------------------------------------------------------------------------------------------

While it's not ideal, sometimes it is required to deploy software based solely off of a list of machines provided by a user. Where I work, I am often given a list of 20-30 machines that need software deployed to them for testing purposes before it goes out to a larger (and more easily targeted) group.

There are multiple ways to handle this. For example, you could put the machines in an OU or an AD member group and create an LDAP label, you could have the users create a text file and create a custom inventory rule, you could upload a csv with an additional field to the asset records of those machines (I have been told this is the supported workaround.), you could type each machine name in manually to the Managed Installation or Script, you could apply a manual label to each machine, etc.

While all of these get the job done, I decided I would post a quick how-to on how I choose to handle this.

In a nutshell, I wrote a Powershell script that takes a list of machine names from a text file (separated by a new line) and generates a SQL query that can then be turned into a Smart Label.

Here is the code (Requires Powershell 5):

#REM This will generate a SQL query for multiple machine names in a text file

Function Get-InputFile($initialDirectory = "C:\") {

    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.Title = "Select the text file containing host names"
    $OpenFileDialog.Multiselect = $false
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "TXT (*.txt)| *.txt"
    $OpenDialogClick = $OpenFileDialog.ShowDialog()

    If ($OpenDialogClick -eq "OK") {

        Return $OpenFileDialog.FileName

    }

    Else {

        Write-Warning "Operation cancelled by user."

        Exit

    }

}

Function Get-SavePath($initialDirectory = "C:\") {

    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null

    $SaveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
    $SaveFileDialog.Title = "Select where to save the SQL Query"
    $SaveFileDialog.initialDirectory = $initialDirectory
    $SaveFileDialog.filter = "TXT (*.txt)| *.txt"
    $SaveDialogClick = $SaveFileDialog.ShowDialog()
    
    If ($SaveDialogClick -eq "OK") {

        Return $SaveFileDialog.FileName

    }

    Else {

        Write-Warning "Operation cancelled by user."

        Exit

    }

}

#Prompt for input text file
$InputFile= Get-InputFile
#Get machine names from specified file
$Machines= Get-Content $InputFile | Sort-Object

if ($Machines -eq $null) {

    Write-Error "The selected text file is blank."

    Exit

}

[int]$MachineCount = $Machines.Count

if ($MachineCount -eq 1) {

    $Prompt = Read-Host -Prompt "The selected text file only has one line. Continue? Yes/No"

    while("yes","no" -notcontains $Prompt) {

    Write-Warning "Please type either ""Yes"" or ""No"""

	$Prompt = Read-Host -Prompt "The selected text file only has one line. Continue? Yes/No"
    
    }

    if ($Prompt -eq "Yes" -or $Prompt -eq "yes") {

        $SavePath = Get-SavePath

        New-Item -ItemType File -Path $SavePath -Force | Out-Null

        Out-File -NoClobber -NoNewline -Append -InputObject "SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE (MACHINE.NAME =""$Machine"")" -FilePath $SavePath

        Exit

    }

    elseif ($Prompt -eq "No" -or $Prompt -eq "no") {

        Write-Warning "Operation cancelled by user."

        Exit

    }

}
#Prompt for path to save output
$SavePath = Get-SavePath

New-Item -ItemType File -Path $SavePath -Force | Out-Null

[int]$Count = 0

Out-File -NoClobber -NoNewline -Append -InputObject 'SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE (' -FilePath $SavePath

foreach ($Machine in $Machines) {

    $Count = ($Count + 1)

    if ($count -eq $MachineCount) {

        Out-File -NoClobber -NoNewline -Append -InputObject "(MACHINE.NAME =""$Machine""))" -FilePath $SavePath

    }

    else {
    
        Out-File -NoClobber -NoNewline -Append -InputObject "(MACHINE.NAME =""$Machine"") OR " -FilePath $SavePath

    }

}

I usually just right click the PS1 and Run with Powershell, but feel free to do what you want with it.

It will first prompt for the input file. This file should be formatted with each machine name on a new line.

Example:

MACHINE1
MACHINE2
MACHINE3

The script will then prompt for the directory and file name to save the output.

After you have the SQL query, you will need to create a Device Smart Label in the K1000.

0Qb5Aj.png  or rTLbqd.png

At this point, we'll just need to create a regular Smart Label. I usually just leave the default search criteria, name the label, and hit Save.

MOhGif.png

Then go to Label Manangement - Smart Labels, and click the link for the new label you created (not the pencil icon).

hGem7o.png

Now click "Edit SQL", and then replace the entire text block with the SQL query from the Powershell script.

hem0y5.png

JqkW46.png

Then hit save, and wait for devices to check in to get the label!

As of right now, I'm not aware of another way to create a SQL smart label. If you have found a better way, then please let me know!

Of course there are downsides to this. The machine won't get the label until next check-in, the label is harder to work with in Inventory, and it's a little less user-friendly to add/remove devices to the label. However, it has worked well for me, and I hope it could prove useful to others.
View comments (2)
Showing 1 - 5 of 6 results

Top Contributors

Talk About App-V Sequencing