Re: Snort + HTSQL dashboard application

[Dan Ferris <dferris () prometheusresearch com>]

No, I don't think that it's equivalent at all.

myapp/?sql= requires the user to actually know enough SQL to query for 
something useful.  HTSQL is a complete query language that assumes no 
knowledge of SQL at all.  You type a URL into your browser and HTSQL 
translates the URL into equivalent SQL statements.

The other nice thing is that HTSQL queries is you can bookmark them, 
email them, IM them, etc.

Here's an example of what I mean, this is ths HTSQL query used to 
generate the Events / Protocol / Hour graph:

http://snort.htsql.org 
/((event?date(timestamp)=today())^{hour(timestamp)}){*1 :as 
Day,count(^.tcphdr) :as TCP,count(^.udphdr) :as UDP, count(^.icmphdr) 
:as ICMP}

This is the SQL equivalent as generated by the HTSQL engine:

  SELECT "event"."hour",
         COALESCE("tcphdr"."count", 0),
         COALESCE("udphdr"."count", 0),
         COALESCE("icmphdr"."count", 0)
  FROM (SELECT CAST(EXTRACT(HOUR FROM "event"."timestamp") AS INTEGER) 
AS "hour"
        FROM "public"."event" AS "event"
        WHERE (CAST(EXTRACT(HOUR FROM "event"."timestamp") AS INTEGER) 
IS NOT NULL)
              AND (CAST("event"."timestamp" AS DATE) = CURRENT_DATE)
        GROUP BY 1) AS "event"
       LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
                               CAST(EXTRACT(HOUR FROM 
"event"."timestamp") AS INTEGER) AS "hour"
                        FROM "public"."event" AS "event"
                             INNER JOIN "public"."tcphdr" AS "tcphdr"
                                        ON (("event"."sid" = 
"tcphdr"."sid") AND ("event"."cid" = "tcphdr"."cid"))
                        WHERE (CAST(EXTRACT(HOUR FROM 
"event"."timestamp") AS INTEGER) IS NOT NULL)
                              AND (CAST("event"."timestamp" AS DATE) = 
CURRENT_DATE)
                        GROUP BY 2) AS "tcphdr"
                       ON ("event"."hour" = "tcphdr"."hour")
       LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
                               CAST(EXTRACT(HOUR FROM 
"event"."timestamp") AS INTEGER) AS "hour"
                        FROM "public"."event" AS "event"
                             INNER JOIN "public"."udphdr" AS "udphdr"
                                        ON (("event"."sid" = 
"udphdr"."sid") AND ("event"."cid" = "udphdr"."cid"))
                        WHERE (CAST(EXTRACT(HOUR FROM 
"event"."timestamp") AS INTEGER) IS NOT NULL)
                              AND (CAST("event"."timestamp" AS DATE) = 
CURRENT_DATE)
                        GROUP BY 2) AS "udphdr"
                       ON ("event"."hour" = "udphdr"."hour")
       LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
                               CAST(EXTRACT(HOUR FROM 
"event"."timestamp") AS INTEGER) AS "hour"
                        FROM "public"."event" AS "event"
                             INNER JOIN "public"."icmphdr" AS "icmphdr"
                                        ON (("event"."sid" = 
"icmphdr"."sid") AND ("event"."cid" = "icmphdr"."cid"))
                        WHERE (CAST(EXTRACT(HOUR FROM 
"event"."timestamp") AS INTEGER) IS NOT NULL)
                              AND (CAST("event"."timestamp" AS DATE) = 
CURRENT_DATE)
                        GROUP BY 2) AS "icmphdr"
                       ON ("event"."hour" = "icmphdr"."hour")
  ORDER BY 1 ASC

On 5/5/2011 4:01 PM, Martin Holste wrote:
> Any kind of tech like that is interesting to me.  I think I'm missing
> why HTSQL is easier for the "accidental programmer."  Isn't it
> functionally equivalent to myapp/?sql=<some sql query>  with a fair
> amount of validation?
>
> On Thu, May 5, 2011 at 4:21 PM, Dan Ferris
> <dferris () prometheusresearch com>  wrote:
> > Thanks for checking. :)
> >
> > So, is it interesting/uninteresting?  I'm curious what others think.
> >
> > Dan
> >
> > On 5/5/2011 2:44 PM, waldo kitty wrote:
> > > On 5/5/2011 16:15, Martin Holste wrote:
> > > > Really?  Weird.  Worked fine on FF4.
> > > >
> > > > On Thu, May 5, 2011 at 9:14 AM, Lay, James<james.lay () wincofoods com>     wrote:
> > > > > http://htsql.org/gallery/snort/index.html
> > > > >
> > > > > Great HORNY TOADS did Firefox not like that page....no less than at
> > > > > least 6 "Error loading element" error popup windows.  Yeesh.
> > >
> > > worked just fine in my FF3.6.17, too...
> > >
> > > james must have some debugging thing loaded... maybe?
> > >
> > > ------------------------------------------------------------------------------
> > > WhatsUp Gold - Download Free Network Management Software
> > > The most intuitive, comprehensive, and cost-effective network
> > > management toolset available today.  Delivers lowest initial
> > > acquisition cost and overall TCO of any competing solution.
> > > http://p.sf.net/sfu/whatsupgold-sd
> > > _______________________________________________
> > > Snort-users mailing list
> > > Snort-users () lists sourceforge net
> > > Go to this URL to change user options or unsubscribe:
> > > https://lists.sourceforge.net/lists/listinfo/snort-users
> > > Snort-users list archive:
> > > http://www.geocrawler.com/redir-sf.php3?list=snort-users
> >
> > ------------------------------------------------------------------------------
> > WhatsUp Gold - Download Free Network Management Software
> > The most intuitive, comprehensive, and cost-effective network
> > management toolset available today.  Delivers lowest initial
> > acquisition cost and overall TCO of any competing solution.
> > http://p.sf.net/sfu/whatsupgold-sd
> > _______________________________________________
> > Snort-users mailing list
> > Snort-users () lists sourceforge net
> > Go to this URL to change user options or unsubscribe:
> > https://lists.sourceforge.net/lists/listinfo/snort-users
> > Snort-users list archive:
> > http://www.geocrawler.com/redir-sf.php3?list=snort-users

------------------------------------------------------------------------------
WhatsUp Gold - Download Free Network Management Software
The most intuitive, comprehensive, and cost-effective network 
management toolset available today.  Delivers lowest initial 
acquisition cost and overall TCO of any competing solution.
http://p.sf.net/sfu/whatsupgold-sd
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users