Results 1 to 5 of 5
  1. #1
    Untangler
    Join Date
    Feb 2018
    Posts
    50

    Default ANDing SQL Conditions for Custom Reports

    When using multiple SQL conditions for custom reports, it appears that OR is the operator between conditions. I'm trying to figure out how to us AND instead, but have come up empty looking for any examples or discussion of this. I am using Settings at the bottom of the screen for a custom report. I want to eliminate all port 443 traffic from my local host to Untangle from the report. Using real IP addresses inside the single quotes, here is what I have:

    Local Address != 'myIPAddr' AND
    Remote Address != 'UntangleIPAddr' AND
    Server Port != '443'

    Without the ANDs in the conditions, the report filters each of these separately, eliminating too much information. When I include the ANDs, I get these responses when I click Update:

    Exception!
    Name: java.lang.RuntimeException: Failed to query database. (490)
    Message: Failed to query database.

    Exception!
    java.lang.RuntimeException: Failed to query database

    CustomReportName report updated!

    I suspect I'm missing something simple but just can't find it. Please advise.

    ~bd

  2. #2
    Untangler
    Join Date
    Feb 2018
    Posts
    50

    Default

    And so I solved this on my own. Instead of using the GUI to create one line for each criterion, I combined all 3 criteria on one line:

    Local Address != 'myIPAddr' AND remote_address != 'UntangleIPAddr' AND s_server_port != '443'

    Except the GUI builds the Column and first Operator into the query, so what I entered into the Value field was only this portion:

    'myIPAddr' AND remote_address != 'UntangleIPAddr' AND s_server_port != '443'

    Hopefully this helps someone else down the road. I'm still baffled as to why I could not find an example of this in the forums or documentation!

    ~bd
    Jim.Alles likes this.

  3. #3
    Untangle Ninja Jim.Alles's Avatar
    Join Date
    Jul 2008
    Location
    Central PA
    Posts
    2,012

    Thumbs up

    Thank you for posting it.

  4. #4
    Untangler
    Join Date
    Feb 2018
    Posts
    50

    Default

    Something else I just noticed is that if Auto Format is enabled for the rule with ANDs in it, saving the changes will fail.

    ~bd
    Jim.Alles likes this.

  5. #5
    Untanglit
    Join Date
    Jan 2020
    Location
    California
    Posts
    29

    Default

    Quote Originally Posted by bdoster View Post
    When using multiple SQL conditions for custom reports, it appears that OR is the operator between conditions.
    That is an unfortunate choice. When I was working within the ecosystem of a large ERP and CRM business system, the general logic to interpret this case was that you'd use OR between multiple conditions for the same field and then AND to concatenate between the conditions for the different fields.

    So

    IP Address = 192.168.1.10
    IP Address = 192.168.1.22
    Server Port = 443
    Server Port = 80

    would be interpreted as
    ( IP Address = 192.168.1.10 OR IP Address = 192.168.1.22 ) AND ( Server Port = 443 OR Server Port = 80 )

    I found that this logic would cover most use cases. Would be nice, if Untangle were to adopt something like this, but of course it would break compatibility with the current implementation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

SEO by vBSEO 3.6.0 PL2