Show HN: FSQL – Search through your file system with SQL-esque queries

Re:Show HN: FSQL – Search through your file system with SQL-... by bpchaps on Monday May 15, 2017 @03:35PM (#14344435)

Nice! I'm actually working on a similar project to push lsof and files from /proc into some postgres tables. Lets me do cool things like query log files across a ~6000 server infrastructure similar to:

  SELECT distinct(l.name) 
    FROM lsof l, lsofer_runs r 
  WHERE l.lsofer_id = r.id 
    AND fd_type = 'REG' 
    AND l.fd ~ '[0-9][uw]' 
    AND l.name like '%log' 
  GROUP BY l.name, r.hostname 
  ORDER BY name

Best of luck!

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by matthewaveryusa on Monday May 15, 2017 @03:40PM (#14344487)

so you're rewriting osquery? https://osquery.io/

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by tyingq on Monday May 15, 2017 @06:12PM (#14345617)

His description sounded like it would do joins across different hosts. Osquery looks to be single host at a time only.

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by bpchaps on Monday May 15, 2017 @07:38PM (#14346176)

Yep.

I'm specifically writing it to find any log file that isn't being pushed into our third party logging service. It's a surprisingly difficult problem, especially considering the amount of tech sprawl that's accumulated. Since it's also a relatively low latency environment, it has to be written in a way that doesn't add too much load (without core isolation..).

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by tyingq on Monday May 15, 2017 @09:10PM (#14346645)

You could use the audit subsystem. https://www.linux.com/learn/customized-file-monitoring-audit...

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by bpchaps on Monday May 15, 2017 @11:45PM (#14347235)

Definitely crossed my mind, but I'm working on hosts where installing auditd isn't really easy. Broken yum and apt all over the place makes installing new packages almost impossible. Same goes for lsof, but its installed in "enough" places. Kinda nightmarish, but it gives me a chance to write some fun code ;).

Also, thanks for the article! Super interesting. Think that'd be better than implementing something on top of sysdig?

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by tyingq on Tuesday May 16, 2017 @12:19AM (#14347323)

Auditd has the advantage of not being intermittent polling, which could miss something. Sounds like it isn't an option though.

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by tyingq on Tuesday May 16, 2017 @08:51AM (#14349126)

Another non polling option: http://www.brendangregg.com/blog/2014-07-25/opensnoop-for-li...

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by bpchaps on Tuesday May 16, 2017 @02:48PM (#14352193)

+1 for anything by Brendan Gregg.

I wasn't aware of the polling limitations of sysdig, but it definitely explains some things I've seen in the past. This is definitely going in my toolkit. Cheers!

Edit: dammit, spelled his name wrong.

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by bpchaps on Monday May 15, 2017 @03:46PM (#14344525)

Hah, apparently.

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by nthcolumn on Monday May 15, 2017 @05:38PM (#14345352)

I need this immediately.

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by bpchaps on Monday May 15, 2017 @07:41PM (#14346190)

I'll see about getting it into a public repo soon and let you know.

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by fomojola on Monday May 15, 2017 @10:36PM (#14346988)

Is this something you could do with Presto? You'd need to write a custom connector and it doesn't look like there is support for dynamically adding/removing catalogs (https://github.com/prestodb/presto/issues/2445) but it would presumably handle the heavy lifting for you.

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by kshvmdn on Monday May 15, 2017 @03:46PM (#14344534)

Looks very cool, is the code available anywhere? Would love to take a look.

[ Parent | on HN ]

Re:Show HN: FSQL – Search through your file system with SQL-... by bpchaps on Monday May 15, 2017 @04:28PM (#14344840)

The code is behind my company's GH, but here's a rewrite of the collector script:

https://github.com/red-bin/lsofer/blob/master/lsofer.sh

[ Parent | on HN ]