Odd chars when using dbUpdate_watchlist.cmd

[RSAAdmin]

I have a txt file that is created via a script that parses an enVision report in csv format.  When I open the txt file with notepad it looks just fine.  When I run >dbUpdate_watchlist.cmd TermedEmp 30dTerm.txt the first entry in the TermedEmp Watchlist is prefixed with three questionmarks.  Attached is the txt file of names that I'm loading into the Watchlist.

Has anyone seen odd chars in the first entry of a Watchlist that they loaded via the dbUpdate script?

[PatrickChouinar]

I believe the problem is in your text file...

I did a quick test with your file as posted and it does give me some weird chars.

When I used "type 30dTerm.txt" in a DOS window, it printed out some weird chars at the beginning of the file. So, I opened the file using Notepad++ and copied/pasted the list of names in a new document and tried again with the new file... TA-DA ! It worked !

I dunno what cause those chars to appear in the first list, but even if you can't "see" the chars in notepad, they were in it anyway.

How was that file created ? Did it come from an automated service ?

[RSAAdmin]

I opened it wirh notepadd++ also but saw nothing.  I didn't think of copying from there into another txt doc.

the source is from an enVision report csv file that i'm parsing with a powershell script i wrote.  The script strips out the header in the csv plus i have to strip out the double quotes.  i'm then writing this array to a txt file.  this is obviously where the problem is coming from.

Here's the powershell code if anyone is a guru I'd appreciate pointing out the possible source.  The AccountChanged is the header from the csv report file

ForEach ($file in $files){
    $array = (Get-Content $file.PSPath)
    foreach ($element in $array) {
    if ($element -notmatch "AccountChanged") {$n_element = $element -replace("`"","")}
        $n_element >> $outfile
    }    

[RSAAdmin]

I found the problem.  Using Powershell script I had to specify "-Encoding "ASCII" so the code should be:

ForEach ($file in $files){
    $array = (Get-Content $file.PSPath)
    foreach ($element in $array) {
    if ($element -notmatch "AccountChanged") {$new_element = $element -replace("`"","")}
        $new_element | Out-file -Encoding "ASCII" -Append $outpath\$outfile
        
    }
    
}

Now that I have this working, I will move the script to our prod system and confirm.  When a employee is termed their account is disable which triggers a Windows Security_642 message.  I run a daily report that outputs a csv file.  The script takes the 30 most current csv files then uploads the past 30 days of termed employees into a watchlist.

[RSAAdmin]

Good catch.    Nice script too.    When you indicated that it "uploads the past 30 days of termed employees into a watchlist" are you calling the dbUpdate_watchlist.cmd and passing it your txt file as indicated earlier in the thread, or have you re-writen the functionality of dbUpdate_watchlist within your powershell script?

Reason i ask is regarding the watchlist size limitation issue, written about in the other thread here in the forum.

thanks

ryan 

[RSAAdmin]

I'm not a Sybase person, but from what I can tell the limit is not a problem of the script but I'd suspect that it's the schema of the watchlist table.  This table has 6 fields; guid, name, description, regex, listvalues, modified.  The listvalues field is the limiting field, this is the field with those odd ~W~ value delimiters.

That said I feel that the watchlist table should be a database and each Watchlist should be table within that database.  So my attempts to creat a 30 day watchlist of terminated accounts list is too large at our company... but I'm still hoping for some sort of workaround.  It's not likely to get any support from the enVsison product engineers

But ehhh....

[RSAAdmin]

Hey RSA (Matt?).  What is the byte size limit of the listvalues field in the watchlist table?

Why?

[RSAAdmin]

   IF

          Enough people scream.

               AND

          Screams are loud enough.

      DO

           Product Enhancement Request

      ELSE

           ECHO "Thanks, we'll look into that." 

I agree with you regarding the need for a dedicated table for watchlists.  I too have quite a few lists i tried to synchronize into enVision watchlists but I could not because of this limitation.   Its a very frustrating challenge with the product.   In fact, I feel so strongly about watchlists and need to make numerous improvements around them, that it calls for a pulse check across the forum community.

If enough of us have a valid requirement for improving them, and by the sounds of others on the forum I think we do, then I truly believe RSA will heavily consider such a request.  

If you agree with  Kurta59's suggestion for a dedicated database for watchlists, containing tables for each watchlist, then Kudo his message so your vote can be heard. 

IMHO the RSA folks with power to steer the product are very good at listening to the user community, but they can't serve everyone's needs for every little request.  I think in this case some serious R&D is warranted.  Especially given the maturity of "watchlist" like functions in competitor products.

[RSAAdmin]

Sorry for the delay, but I went and checked on this.  I have been told:

1) There is no limit to how many characters a particular watchlist value can be

2) There is no limit to how many values per watchlist

3) There is no limit to how many watchlists you can create

Based on that, if you are seeing truncations when you try to import lists from a file, I would open a support case.

[RSAAdmin]

No limit?  Really? Then the Sybase update command has a limit. Here is a quick rundown of what the script does. 

The dbUpate_watchlist script reads a file and creates one string named WATCHLIST here:

for /F "tokens=1" %%i in (%INPUTFILE%) do call :Append_CMD %%i 

:Append_CMD
set WATCHLIST=%WATCHLIST%%1~W~

Then it creates a .sql file to update the watchlist table to modify an existing Watchlist created from the GUI. 

update "watchlist"  set listvalues = '%WATCHLIST%' where name = '%WATCHLISTNAME%';

I described the watchlist table's fields previously in this thread. The script modifies the record matching the Watchlist name via the Sybase update and set functions to make the listvalues field to be the WATCHLIST string.

It has been discussed in other sections of this board that people have run into a limit on the number of items that they can to load into a Watchlist via this script.  This number of items actually turns out to be one single string of the users items delimited with ~W~ ie: the variable %WATCHLIST% becomes joe~W~bob~W~carol~W~ etc.

I'm not a Sybase person but from a slightly more generic database view indicates that there is a bottleneck here of either the CHAR (byte) size of the listvalues field maximum and/or the Sybase update command has a limit to the length of the string of it's 'set' parameter.  Or both.

Is dbUpdate_watchlist.cmd an RSA supported script?