Results 1 to 8 of 8
  1. #1
    VFD
    VFD is offline
    Newbie
    Join Date
    Dec 2020
    Posts
    3

    Default Confitional filter for client subnet

    Trying to set up a conditional filter in the reports section to filter for clients in a specific subnet. I've figured out that client records (c_client_addr) are postgresql type inet, but the <<, <<=, >>, and >>= operators for performing a subnet-based query aren't present in the operator drop down menu. Any other operators such as 'like', <, or = either return no results or throw DB errors. Those outcomes aren't entirely unexpected though since those aren't specifically intended to process subnet masks for inet data types.

    Without having to use the search filter every time I switch between individual report pages, is there a way to use conditions filtering to filter for a range of IPs or entire subnets for all reports?

  2. #2
    Untangle Ninja sky-knight's Avatar
    Join Date
    Apr 2008
    Location
    Phoenix, AZ
    Posts
    26,119

    Default

    Yeah something is wrong with reports on this.

    https://wiki.untangle.com/index.php/Operators

    LIKE is clearly what you want here, and you should be able to say 10.10.10.% to match 10.10.10.*, even the examples used in the wiki detail this. But yet, on my v16.1 install right now you're right, SQL errors.

    And if the datatype is inet in Postgres, there's a whole slew of special stuff to use that can simply accept a CIDR block, but those aren't working either.

    We need a << operator?

    Code:
    SELECT * FROM users WHERE ip << inet '88.99.0.0/16';
    I think in the past you could just do = and truncate the IP address, but that's not working either.
    Last edited by sky-knight; 12-29-2020 at 02:56 PM.
    Rob Sandling, BS:SWE, MCP
    NexgenAppliances.com
    Phone: 866-794-8879 x201
    Email: support@nexgenappliances.com

  3. #3
    VFD
    VFD is offline
    Newbie
    Join Date
    Dec 2020
    Posts
    3

    Default

    I'm on Home Protect Plus w/o a support entitlement, so the initial/only response I got from Untangle was that I'm not using the right syntax + just use per-report search. The correct syntax is wasn't mentioned in their reply, but the wiki indicates '%' for wildcarding should be appropriate.

    Adding the << operator for records that are inet typecast seems to be the correct way to resolve this.

    That aside, the weird DB errors on wildcard (% or * chars) based searches using 'like' don't really make sense to me since that should just be a string match operation. Maybe something funny going on in input validation that results in a non-standard rejected query.

  4. #4
    Untangler
    Join Date
    Aug 2018
    Posts
    55

    Default

    The best way to search for a specific subnet is to use the > and < operators. Example: if you want to see anything from 10.10.10.0/24 subnet, you would have 2 conditions. 1 for client > 10.10.10.0 and 1 for client < 10.10.10.255.

    This isn't ideal, but it works. "Like" doesn't play well with inet values.

  5. #5
    VFD
    VFD is offline
    Newbie
    Join Date
    Dec 2020
    Posts
    3

    Default

    I'd call that a workaround more than a solution though. Virtually everything I manage can understand CIDR notation, so it feels a bit antiquated to have to expand the mask out for Untangle reports.

    Assuming Untangle conditions are predominantly string matching operations, It'd probably work better if a postgres CAST to bit/char/text is used when searching inet type records, especially for 'like' queries. Native inet '<<' operator queries are probably more efficient though.

  6. #6
    Untangle Ninja sky-knight's Avatar
    Join Date
    Apr 2008
    Location
    Phoenix, AZ
    Posts
    26,119

    Default

    Yeah that does work, but it's harder than it needs to be and honestly... used to be.

    We need the inet operator, because the Postgres query to do this is stupidly easy with CIDR input.
    Rob Sandling, BS:SWE, MCP
    NexgenAppliances.com
    Phone: 866-794-8879 x201
    Email: support@nexgenappliances.com

  7. #7
    Untangler
    Join Date
    Aug 2018
    Posts
    55

    Default

    I agree that it is not ideal. I haven't tried all of the possible operators that work for inet, so if you find something that works better, please let us all know!

    One thing you will want to keep in mind when testing, is the auto-format checkbox. You will want to try your query with that checked and unchecked and it may allow you to do what you are trying to do.

  8. #8
    Untangle Ninja sky-knight's Avatar
    Join Date
    Apr 2008
    Location
    Phoenix, AZ
    Posts
    26,119

    Default

    Quote Originally Posted by tcurtis View Post
    I agree that it is not ideal. I haven't tried all of the possible operators that work for inet, so if you find something that works better, please let us all know!

    One thing you will want to keep in mind when testing, is the auto-format checkbox. You will want to try your query with that checked and unchecked and it may allow you to do what you are trying to do.
    The operator was listed in this thread! I put a code block up there straight out of Postgres's wiki

    Code:
    SELECT * FROM users WHERE ip << inet '88.99.0.0/16';
    That is how you're supposed to select a CIDR IP range from an inet datatype.

    We're missing the << operator in the list on Untangle.
    Rob Sandling, BS:SWE, MCP
    NexgenAppliances.com
    Phone: 866-794-8879 x201
    Email: support@nexgenappliances.com

Tags for this Thread

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