Re: best automated way to construct a timeline from websense logs?

[Chris Campbell <chris () ctcampbell com>]

Out of interest, what where the problems you had with splunk? This looks 
like exactly the kind of problem it was designed to solve.

allison nixon wrote:
> I got it to work.  I ended up using mysql and some command line 
> shenanigans
>
> For the benefit of everyone who might be faced with 40 gigs of log 
> files, I ended up doing this:
>
> use split -l 5000 * to split every file into a reasonable sized chunk
>
> then used ls -l to get a list of file names in the folder in a nice 
> orderly fashion
>
> then created a sql database and a table called client, and set every 
> column type to the sort of data it would end up holding
>
> then write a bash script that was like below. the commands were 
> slightly altered based on the name of every file, so the script had 
> about 750 lines in total.  there's probably a more elegant way to do 
> this, with fancy looping and variables, but no time for that.
>
> ln -s datetime-websenselog.csvaa client.txt; mysqlimport 
> --fields-terminated-by=, --lines-terminated-by="\r\n" -u user 
> --password=password --fields-optionally-enclosed-by="\"" 
> --columns=id,userid,hostid,wdate,wtime,wuts,srcip,srcport,dstport,dstip,resource,bytes,xfertime,code,category,allowed,hid,hostname,uid,username 
> client 
> /root/Desktop/client/tobeanalyzed/Files/raw/splitted/client.txt; rm 
> client.txt;
>
> the symbolic link is necessary because mysqlimport will only put the 
> file into the table of the same name
> then i had to tweak it till the warnings went away, because 
> mysqlimport won't tell you the contents of those warnings, only that 
> they have been raised.  after some guessing games, I found out some 
> but not all fields were enclosed with "
>
> Now i can run sql queries and it's somewhat trivial to find the 
> information i'm after now!
>
> On Sun, Jun 9, 2013 at 2:07 PM, Champ Clark III 
> <cclark () quadrantsec com <mailto:cclark () quadrantsec com>> wrote:
>
>     -----BEGIN PGP SIGNED MESSAGE-----
>     Hash: SHA1
>
>     Actually thinking about this liblognorm might be useful. It comes with
>     a program call "normalizer".  You'll need to create the rulebase
>     files/rules.  That'll be the tricky part.
>
>     If you do create good rulebase/rules, let me know. I'd like to have a
>     copy :)
>
>
>     On 6/9/13 1:16 AM, Johan Peder Møller wrote:
>     > Have looked at liblognorm. No personal experience, but remeber
>     > having it recomended at some time.
>     >
>     > rgds Johan
>     >
>     >
>     > On Fri, Jun 7, 2013 at 3:36 AM, allison nixon <elsakoo () gmail com
>     <mailto:elsakoo () gmail com>
>     > <mailto:elsakoo () gmail com <mailto:elsakoo () gmail com>>> wrote:
>     >
>     > So I have several gigs of webnonsense logs and I am trying to
>     > construct a timeline of malware infection as it spreads from IP to
>     > IP.  I already know what the malicious URLs look like so that's
>     > not the issue.  I want to be able to build a timeline of activity
>     > to describe the first moment a computer was infected and I want to
>     > illustrate when the phone home traffic hops from domain to domain.
>     >
>     > I can sort of do it with some artful use of grep and excel, but
>     > it's hard to make that scale to more than a small sample of the
>     > logs.  I fed it to a trial copy of Splunk and it exploded while
>     > giving me nothing useful.  Are there any tools out there that I can
>     > use for this?  I don't want to pay money for it because it's a
>     > one-off, but so far nothing can compete with good ol grep
>     >
>     > _______________________________________________ Pauldotcom mailing
>     > list Pauldotcom () mail pauldotcom com
>     <mailto:Pauldotcom () mail pauldotcom com>
>     > <mailto:Pauldotcom () mail pauldotcom com
>     <mailto:Pauldotcom () mail pauldotcom com>>
>     > http://mail.pauldotcom.com/cgi-bin/mailman/listinfo/pauldotcom Main
>     > Web Site: http://pauldotcom.com
>     >
>     >
>     >
>     >
>     > _______________________________________________ Pauldotcom mailing
>     > list Pauldotcom () mail pauldotcom com
>     <mailto:Pauldotcom () mail pauldotcom com>
>     > http://mail.pauldotcom.com/cgi-bin/mailman/listinfo/pauldotcom Main
>     > Web Site: http://pauldotcom.com
>     >
>
>
>     - --
>     - - Champ Clark III (cclark () quadrantsec com
>     <mailto:cclark () quadrantsec com>)
>       Quadrant Information Security (http://quadrantsec.com)
>       Key Fingerprint: 2E56 C2EB 1B25 C517 D5BA 2DCF 5E70 B2F8 0381 878A
>       GPG Key ID: 0381878A
>     -----BEGIN PGP SIGNATURE-----
>     Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
>     Comment: GPGTools - http://gpgtools.org
>     Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
>
>     iQEcBAEBAgAGBQJRtMRGAAoJENnmXt7Lmc3KiJgH/A42nLvCPYqs4y3ULZrj3rLz
>     WUgdNJ9UjM7eeZt1qdiA4Jx7h51Y0opco+bMwcqoIiccDxqOjqRxf3FxqMyOKCT6
>     +/nQDRu132mtfkw5vXLtNt2eZaAu28pRU72XkuoGMn9D6B1d/9pheLYtsz7AnfcL
>     Zf0ZXeE5oPBFF73/BsVuzsIbE2Ia2a6G5pS/H77vYmxQXb7Dp/BoQl/hUoxAzyoH
>     8EnwzueRraWoZBetZb+o5auoaa0MVYY3NffEPNybXzaxfpTFgMs90RJo8Up3dqQN
>     ksYxIhqXe4EF1I5eCvV4ugjE1FRvKP9pqTawDSQVjnT7RjzFjsUhUMZPwBMDnM0=
>     =Uw5n
>     -----END PGP SIGNATURE-----
>     _______________________________________________
>     Pauldotcom mailing list
>     Pauldotcom () mail pauldotcom com <mailto:Pauldotcom () mail pauldotcom com>
>     http://mail.pauldotcom.com/cgi-bin/mailman/listinfo/pauldotcom
>     Main Web Site: http://pauldotcom.com
>
>
>
>
> --
> _________________________________
> Note to self: Pillage BEFORE burning.
> _______________________________________________
> Pauldotcom mailing list
> Pauldotcom () mail pauldotcom com
> http://mail.pauldotcom.com/cgi-bin/mailman/listinfo/pauldotcom
> Main Web Site: http://pauldotcom.com
_______________________________________________
Pauldotcom mailing list
Pauldotcom () mail pauldotcom com
http://mail.pauldotcom.com/cgi-bin/mailman/listinfo/pauldotcom
Main Web Site: http://pauldotcom.com