This year, I was selected as a “SANS @Night” presenter at SANSFIRE 2011, in Washington DC. The SANS Institute (System Administration and Network Security) is the largest provider for information security training and security certification in the world.
My presentation, entitled “SQL Ginsu: Better Living (and Data Reduction) Through Databases”, focused on how a digital forensicator can use an SQL database as a tool to increase their analytic effectiveness. This skill is even more important today, as we are faced with ever-growing volumes of data from more and more sources during investigations.
It was a lot of fun to put the material and examples together, and I appreciate the opportunity to have spoken at SANSFIRE this year. Click through to find the presentation as well as two python scripts used to populate the databases in the two examples we covered. I hope that you found the presentation useful. Thanks to all those who attended, and to SANS for the opportunity to speak.
Python scripts (remove the “.txt” extension”):
- Load login data from Linux “last -i” command: wtmp_ripper.py
- Load packet data with SANCP: pkt_to_db_load.py
Phil–
Great presentation. I am a huge proponent of using SQL databases during incident response as well… it’s the only way to tame the beast of the huge data problem. I’ll have to post up my python scripts for parsing ASA and OWA logs somewhere.
My personal favorite DB for doing this work is postgresql, as it provides a proper IP address data type so you can do queries on subnets, etc and the backend storage is still a 4-byte integer (for ipv4 at least). Also one of my favorite tricks is to create a separate table with artifacts of interest (say “bad” IP addresses) with a free form “notes” field. I can then join that “notes” table against any other table with a field in common. Comes in real handy for reporting. Connecting to Excel through ODBC makes for a real easy pretty report table generator.
@Jeff: Bummer that the schedule didn’t work out, but hope you were able to get some good info from it in this form.
@Jason: Sweet! I have yet to make the jump to PostgreSQL, but the functionality you describe (among other reasons) make it a compelling choice. And I *love* the idea of joining against something with a notes field. Just gave me a retroactive idea that would have made a long-term case much, much easier to correlate. Might have to give it a try… Thanks for checking it out and commenting!
Glad you put this up, Phil. Had to head home from SANSFIRE on Thursday so I couldn’t be there for your presentation. Thanks!
Phil,
The talk you presented was awesome!!!
Thanks, Willie – I’m glad you enjoyed it!