Updating Watchlist Contents

[RSAAdmin]

The following script is used to update a watchlist using a text file as an input source.  For example you would like to nightly create a report of disabled accounts then have them be automatically added into the disabled accounts watchlist, you would use this script to read the txt file and inport them into the watchlist

[MadanSudhindra]

Hello Dave,

The utility is very interesting.

I have several watchlists that I would like to update regularly, probably once every 2 hrs.

For example, I have a watchlist named "Botnet" which lists all the known Botnet C&C servers on the Internet. I would like this watchlist to be updated using a file "botcc_temp.txt" which is produced by a script I wrote.

However, I run into errors when I try to run this command.

The problem I face when executing this command is as follows -

The first time I attempted to run the command, the same ran successfull, without any problems.

Every subsequent time I try running the "dbUpdate_watchlist.cmd" command, I get an error like

E:\nic\3700\LOGILOG-AS1\database\cmd>dbUpdate_watchlist.cmd botnet botcc_temp.txt
The input line is too long.
:Append_CMD
 was unexpected at this time.

The syntax I am using to update the "botnet" watchlist is:

dbUpdate_watchlist.cmd botnet botcc_temp.txt

What could be the problem here ?

The botcc_temp.txt file contains multiple IP addresses, with each IP address on a single line.

Please help me solve this issue ?

[RSAAdmin]

Hi Madan,

Thanks for your post! I am not sure why you're running into this error, but please submit a ticket with Secure Care Online. This site is meant to be used for sharing information and knowledge and does not take the place of SCOL. 

Best,

Debbie Umbach (RSA) 

[RSAAdmin]

It sounds like I'm doing something very similar. I'm receiving the Botnet C&C output from ShadowServer.org, and using this with alerting.

It appears the  dbUpdate_watchlist.cmd   follows the following process:

1. Take your input file, and read in each line, and create a single line output, with each of your values separated by ~W~   for the database field. 

2. It then generates a sql script   load_watchlist.sql   which is then meant to be run, to inport the data. 

The problem appears to be, if you have a large input file ( mine is about 29k) when it tried to build the sql script, it's using echo commands to build the file. It appears to be either overflowing the environment space for the %WATCHLIST% variable or hitting the maximum for how long a command line is allowed to be.

I don't think it was designed to allow really large watchlists. If you look at the database, the field for the data is only 32K in size.

[RSAAdmin]

I've seen the same thing when I syncronize several of my really large watchlists.  I have a series of crappy scripts I wrote that query active directory for specific OU's and groups, formats the results accordingly, and then calls the db_update_watchlist to commit them into envision watchlists.  I then schedule these to run every hour, so the watchlists are somewhat dynamic and semi-up-to-date with AD.   A couple of them have had this problem when the list got up to a couple hundred entries.  I agree in that it seemed to have to do with the length of the %watchlist% variable reaching some limit.

Did you ever open a case with tech support, and if so, was it successful in getting a resolution for this issue?  If so, I'll do the same myelf.

thanks

ryan

[MadanSudhindra]

Hi Ryan,

No, I never found a workaround or fix to this problem.

I could'nt open a case with RSA support as this is not an "officially supported" utility. Though, such a utility will be extremely useful.

The problem is they way that the script builds the watchlist, using echo commands.

I dont think RSA ever imagined that watchlists could ever get big.

Im really hoping they find some kind of fix to this.

[RSAAdmin]

Well... it turns out there is yet ANOTHER size limitation when updating watchlists.

OK, so tonight my goal was to synchronize (one of several) really large watchlists from our enterprise AD environment into an enVision watchlist.  The specific target list has user names in it, the majority of them 8 characters in length.  Sounds pretty strait forward I suppose, nothing too overly complex. I wrote a script to pull samaccountname from AD and populate a txt file in the right format.  Successfully tested script... 3,345 usernames.  Sweet! This in itself was a raw character count of 32,721  Replaced dbUpdate_watchlist.cmd with dbUPdate_Watchlist.ps1 (Courtesy of Kurta59, thanks man!)  Ran the update script passing my watchlist name and input file and....  well, nothing.   Script finished, watchlist was empty.  Hmmm.

Alas, Google failed me not and I found a few interesting tidbits pertinent to this topic I thought I'd pass along.  Things like...

- In Windows Server 2003, CMD.exe has a environment variable string length limitation of a maximum of 8,191 characters.  

- Above and beyond any application limitations, the Windows 2003 operating system itself also has an environment variable size limitation of 32,767 characters.   Bummer!

In the case of powershell, both the shell environment AND programming language, it has no documented string limitation that i could find, it basically goes all the way up to the OS's ceiling, and dies at 32,767.   When i did the calculations, it turns out my 3,345 lines  of usernames (+ 3 chars per line for the ~w~) ended up generating a 42,756 long string of characters.  It of course failed because of the OS, and I slowly fuzzed it back down till it finally fired, coincidentally just under the OS limitation of 32,767.  Walla... watchlist was populated.  Thanks again Kurt, that script rocks.

In passing, I've found the following holds true... at least for this week.
1. Both scripts will continue to serve you well. 
2. The CMD version will have a string limitation of 8,191.  Still very good for those smaller watchlists.
3. The powershell version will get you up there loading lists in the several if not tens of thousands of entries.
4. The method of trying to cram everything into a single string and then executing upon it works well for small lists, but there are always going to be limitations. 

Out of curiosity, is anyone else trying to populate watchlists with 4 or 5 thousand entries or more?  What has your success been?  Does the above mentioned limitation impact your use of enVision?

Cheers,
ryan

[RSAAdmin]

Hi Dave,

This script worked to update the watchlist, Thanks. But I have a report that is looking at this watchlist and it now does not filter on what is in the watchlist. If I change to another watchlist that I created manually it does filter on those IP's.

Please help this is a critical process that we need to define.

Thanks,

Ken Greene

[RSAAdmin]

  Cancel my last post. it would help if I updated the watchlist with devices that were actually in out monitored devices.

Thanks.

[RSAAdmin]

I would do this with ADO using a VBScript (preferably in an Windows Script File).  There would be no limitation on size and you can better handle spaces and regular expression lists.  I first saw this code today and realized that it is simply updating one field in the Sybase database.

For example, you could:

• Create a Connection and Recordset object.
• Pull the data for the related watchlist into the recordset.
  • You could split to an array or add the data to a hash table to work only the differences or...
  • You could simply replace the record.
• Put the updated data back into the database.
• Close, clear and notify or log.

It would surely handle the following better:

• Errors.
• Logging and notification.
• The volume of data would only be limited by the database.
• Spaces and special characters wouldn't matter as you can use parameters and handle exceptions.
• You could make the whole thing work with regular expression if needed.

I will work with Mr. Glover on the file if he is interested in trying it.  I have already used VBScript/ADO to create exports of scheduled reports (binds XML for setup, Sybase for Schedule, XML for device groups) so it should be pretty easy to recycle most of that work.

You could also better handle the list file data and even use another database as a source.  If you use a list file, you could use array functions such as Join() and Split() to optimize the setup.

Example:

set fso = CreateObject("Scripting.FileSystemObject")
set listfile = fso.OpenTextFile("List.txt")
listArr = split(listfile.readall,vbNewLine)
watchlist = join(listArr,"~W~")
wscript.echo watchlist