You could make the CmdEntered field unique, or create a hash on the uppercase content of the command and make that a unique key.
Then use INSERT OR IGNORE... Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 2 February 2017 at 16:22, Stephen Chrzanowski <pontia...@gmail.com> wrote: > By a new requirement of my manager, we're asked to log all our SSH sessions > to our customer machines. The current Windows search is a PITA, grepping > for text is burdensome considering the number of sessions I open per day, > and being a pack rat, I love reading about stuff I did years ago. :] (Not > to mention the CYA thing is primary reason for this move -- I'm not > complaining) > > So I'm thinking about writing a tool that'll take the output of the PUTTY > logs, read them line by line, and insert the data into a SQLite database > with some referential integrity that will allow me to search against what > server I'm connecting to, a range of dates the logs, particular text, etc. > (Granted there is a huge range of error that could happen with this that > I'm not anticipating, but, meh. I need something) > > Getting the data from the logs into a database is a true no-brainer. Same > with parsing and deciding how I want to check for text and properly catalog > what I'm doing per machine. Some putty sessions I jump between several > machines, so during the line reading, I'll be looking for keywords > (Hostnames) based on the command prompt since how our prompts are globally > the same across all machines. > > During the reading process, what I want to do is read the line in, check > the database to see what I've read in already exists and react accordingly > by adding the new entry and setting up the relationships in other tables. > Childs play, IMO. > > But, in my preplanning, scenario development and brain storming, the above > paragraph is going to destroy my machine doing a [ select * from CmdLine > where upper(CmdEntered) =upper('SomeText') ] every time I read a new line > from a new log file to verify if the entry has been made. So my thought > leans towards FTS, but, I've never written anything dealing with that. > > Is there any kind of special preparation I need to do to the database to > get it working effectively? Is there a particular way I have to make my > queries to see if previous text exists? Is there a primer, not on the > theory of how it works in the back end, but, how to generate the SQL call > and deal with what comes out? Are there any caveats I need to be aware > of? Do I skip FTS and just roll my own word analyzer? > > Since Oct 2016, my logs are sitting at just shy of 700meg of text. Looking > for what I did on a particular machine even last month would be a pain at > this point. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
