Quantcast

Filter results based on contents of another single field table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Filter results based on contents of another single field table

Stephen Chrzanowski
I'm attempting to get a report given by TrustWave trimmed down to results
that can be more easily managed.  I've taken the results of a report,
cleaned it up with Excel, then used SQLite Expert to import that result
into a database.

Here are the two table DDLs:

CREATE TABLE [SkipRemed] (
  [Skip] CHAR);


CREATE TABLE [TWScan] (
  [ExtIP] CHAR,   [IntIP] CHAR,   [Service] CHAR,   [VulnName] CHAR,
[Desc] CHAR,
  [Remediation] CHAR,   [Port] CHAR,   [Severity] CHAR,   [CVE] CHAR,
[Ticket] INTEGER);

There's only 2049 results in TWScan, so I'm not concerned about speed, and
there is no direct relationship between the two tables.  That is the entire
DDL for the entire database.

What I want to do is use SkipRemed to "Filter Out" or "Filter For" results
in TWScan based on the Remediation table.  The difference between Out and
For would be just in the calling query.  Right now, I want to filter OUT
results in SkipRemed so I can more easily see what reports are commonly
themed and result in one action being required to correct.  (IE: Upgrade
PHP or Apache).

The query I've been messing with is this:

*select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
Remediation not like (select distinct Skip from SkipRemed) order by
upper(Desc),upper(Service)*

I don't get the results I want, unless I use the actual full text of the
Remediation text.  I've changed Skip to '%'||Skip||'%' in the subquery but
that doesn't get me the results I want either.

Somethings up with my logic, not sure where.  Ideas?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Filter results based on contents of another single field table

Simon Slavin-3

On 19 Apr 2017, at 8:12pm, Stephen Chrzanowski <[hidden email]> wrote:

> The query I've been messing with is this:
>
> *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> Remediation not like (select distinct Skip from SkipRemed) order by
> upper(Desc),upper(Service)*

I would have expected

select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
Remediation NOT IN (SELECT Skip from SkipRemed) order by
upper(Desc),upper(Service)

> I don't get the results I want, unless I use the actual full text of the
> Remediation text.  I've changed Skip to '%'||Skip||'%' in the subquery but
> that doesn't get me the results I want either.

What are the affinities of the Remediation and Skip columns ?

What is it doing ?  Skipping ones you want to include, or including ones you want to skip ?  Both ?  Can you give examples of the Remediation and Skip values ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Filter results based on contents of another single field table

Stephen Chrzanowski
As I understand it, "IN" presents an exact match, case sensitive
comparison.  Using LIKE was thought to get a list of substrings that could
be anywhere in any string found within the Skip field.  So if a row in the
Skip field contained "%Apache HTTP%" then I'd like to see, or not see,
depending on the NOT being present in the outer query, the appropriate
results.  For example, if I did not want to see any Remediation with the
text "Apache HTTP", I'd enter as a row in Skip "Apache HTTP" or "%Apache
HTTP%" and the query I provided would give me all results that do NOT
contain "Apache HTTP" in PMEScan.  The "Not seeing" portion was to get rid
of visual garbage so I can further refine the result set, and make sure I'm
adding criteria I need to tag into the ticket.

Everything is plain text, defined in the table def as just CHAR, probably
stored as Latin1 or UTF8, except the Ticket which is defined as an
integer... but.. knowing SQLite and it not really caring about field
types....

An example row in TWScan (Renamed from PMEScan) would be:

ExtIP    IntIP    Service    VulnName    Desc    Remediation    Port
Severity    CVE    Ticket
x.x.x.x   y.y.y.y    SomeWebService_Public    "Apache HTTP Server
mod_log_config Denial of Service Vulnerability"    "Apache HTTP Server
contains a vulnerability in the mod_log_config component that could allow a
remote attacker to crash the web server."    "This vulnerability was fixed
with the release of versions 2.4.9 and 2.2.27 of Apache HTTP Server.
However, it is recommended that you upgrade to the latest available
release."    443    M    CVE-2014-0098    0


An example row in SkipRemed would be
Skip
Apache HTTP
%Apache HTTP%

The query I'm using to get the list of services & existing Remediations:

select distinct VulnName,Remediation from TWScan where Remediation not in
(select distinct Skip from SkipRemed) and Severity in ('M','H') and Ticket
is null order by Remediation like '%Apache%' desc, upper(Remediation);

(This sorts anything to do with Apache near the top of the result list,
then sorts based on text)


On Wed, Apr 19, 2017 at 3:30 PM, Simon Slavin <[hidden email]> wrote:

>
> On 19 Apr 2017, at 8:12pm, Stephen Chrzanowski <[hidden email]>
> wrote:
>
> > The query I've been messing with is this:
> >
> > *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> > Remediation not like (select distinct Skip from SkipRemed) order by
> > upper(Desc),upper(Service)*
>
> I would have expected
>
> select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> Remediation NOT IN (SELECT Skip from SkipRemed) order by
> upper(Desc),upper(Service)
>
> > I don't get the results I want, unless I use the actual full text of the
> > Remediation text.  I've changed Skip to '%'||Skip||'%' in the subquery
> but
> > that doesn't get me the results I want either.
>
> What are the affinities of the Remediation and Skip columns ?
>
> What is it doing ?  Skipping ones you want to include, or including ones
> you want to skip ?  Both ?  Can you give examples of the Remediation and
> Skip values ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Filter results based on contents of another single field table

David Raymond
In reply to this post by Simon Slavin-3
I don't think like works with a subquery as its righthand operand. Or at least not the way you're expecting it to. It's probably only using the very first result of the subquery for all the comparisons. If you're looking for an exact match then what Simon suggested is the way to go. If you're going to have actual match patterns in SkipRemed then you're going to need a join in there.



On 19 Apr 2017, at 8:12pm, Stephen Chrzanowski <[hidden email]> wrote:

> The query I've been messing with is this:
>
> *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> Remediation not like (select distinct Skip from SkipRemed) order by
> upper(Desc),upper(Service)*

I would have expected

select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
Remediation NOT IN (SELECT Skip from SkipRemed) order by
upper(Desc),upper(Service)

> I don't get the results I want, unless I use the actual full text of the
> Remediation text.  I've changed Skip to '%'||Skip||'%' in the subquery but
> that doesn't get me the results I want either.

What are the affinities of the Remediation and Skip columns ?

What is it doing ?  Skipping ones you want to include, or including ones you want to skip ?  Both ?  Can you give examples of the Remediation and Skip values ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Filter results based on contents of another single field table

R Smith
In reply to this post by Stephen Chrzanowski

On 2017/04/19 9:12 PM, Stephen Chrzanowski wrote:

> I'm attempting to get a report given by TrustWave trimmed down to results
> that can be more easily managed.  I've taken the results of a report,
> cleaned it up with Excel, then used SQLite Expert to import that result
> into a database.
>
> Here are the two table DDLs:
>
> CREATE TABLE [SkipRemed] (
>    [Skip] CHAR);
>
>
> CREATE TABLE [TWScan] (
>    [ExtIP] CHAR,   [IntIP] CHAR,   [Service] CHAR,   [VulnName] CHAR,
> [Desc] CHAR,
>    [Remediation] CHAR,   [Port] CHAR,   [Severity] CHAR,   [CVE] CHAR,
> [Ticket] INTEGER);
>
> There's only 2049 results in TWScan, so I'm not concerned about speed, and
> there is no direct relationship between the two tables.  That is the entire
> DDL for the entire database.
>
> What I want to do is use SkipRemed to "Filter Out" or "Filter For" results
> in TWScan based on the Remediation table.  The difference between Out and
> For would be just in the calling query.  Right now, I want to filter OUT
> results in SkipRemed so I can more easily see what reports are commonly
> themed and result in one action being required to correct.  (IE: Upgrade
> PHP or Apache).
>
> The query I've been messing with is this:
>
> *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> Remediation not like (select distinct Skip from SkipRemed) order by
> upper(Desc),upper(Service)*
>
> I don't get the results I want, unless I use the actual full text of the
> Remediation text.  I've changed Skip to '%'||Skip||'%' in the subquery but
> that doesn't get me the results I want either.
>
> Somethings up with my logic, not sure where.  Ideas?

SELECT DISTINCT ExtIP, IntIP, Service, Remediation
   FROM PMEScan
  WHERE NOT EXISTS(SELECT 1 FROM SkipRemed WHERE Remediation LIKE Skip)
    AND ...
  ORDER BY ...


Should do it.
Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Filter results based on contents of another single field table

Brian Curley
I recently had a case where I needed to stack the strings that I did want,
minus those that I didn't. The in() and like() options weren't all that
flexible given their assumption of known strings and I needed to
accommodate near-hits. I found that group_concat() and regexp() work pretty
well together, so that you can have offsetting populations:

First, arrange two views, one with the strings you're seeking, then another
with those you're skipping. The subquery here uses a reference table
(tbl_misc) and so it's optional, but allows for an external maintenance
point...in case this is a persistent situation. One caveat though: regexp()
is intentionally limited in the general release, and a little flaky when it
comes to collations. It does handle ^string vs string$, but only if they're
placed accordingly at the head or back of the line...

SELECT DISTINCT

-- Select only column_name values that match [SEEK|SKIP] list in
tbl_misc...

column_name

FROM

vw_OD_distinct_columnnames

WHERE

column_name

-- Works as hard-coded list...

-- REGEXP ('_BEG_|addr|init|loan|merch|name|score|ssn|_END_')

-- Initially failed to match as a flattened resultset...

-- Resolved by appending a dummy string...and matching suffix (_BEG_ ..
_END_)

REGEXP (

SELECT

"'_BEG_|" || group_concat(code_val,"|") || "|_END_'" regex_arr

FROM (

SELECT

code_val

FROM

tbl_misc

WHERE

code_key

== 'SEEK'  -- alternatively 'SKIP'

AND attrib

IS NULL

GROUP BY

1

)

)

AND ( -- Only select columns that are well-formed, ie "_" or alphabetical
start...

unicode(lower(column_name))

== 95

OR

unicode(lower(column_name))

between 97

and 122

)

ORDER BY

-- Eliminate case factors in sorting...

CASE WHEN unicode(lower(column_name)) < 95

THEN unicode(lower(column_name)) + 32

ELSE unicode(lower(column_name))

END

,1


​Then a subsequent view joins the two together, using EXCEPT:

SELECT DISTINCT

-- Streamlined list of column_names, based on _seek and _skip...

column_name

FROM ( -- Outer query required to offset quirk that prevented ORDER BY
below...

SELECT

*

FROM

vw_sub_strings_seek

EXCEPT

SELECT

*

FROM

vw_sub_strings_skip

)

ORDER BY

CASE WHEN unicode(lower(column_name)) < 95

THEN unicode(lower(column_name)) + 300

ELSE unicode(lower(column_name))
END​


Regards.

Brian P Curley



On Wed, Apr 19, 2017 at 4:47 PM, R Smith <[hidden email]> wrote:

>
> On 2017/04/19 9:12 PM, Stephen Chrzanowski wrote:
>
>> I'm attempting to get a report given by TrustWave trimmed down to results
>> that can be more easily managed.  I've taken the results of a report,
>> cleaned it up with Excel, then used SQLite Expert to import that result
>> into a database.
>>
>> Here are the two table DDLs:
>>
>> CREATE TABLE [SkipRemed] (
>>    [Skip] CHAR);
>>
>>
>> CREATE TABLE [TWScan] (
>>    [ExtIP] CHAR,   [IntIP] CHAR,   [Service] CHAR,   [VulnName] CHAR,
>> [Desc] CHAR,
>>    [Remediation] CHAR,   [Port] CHAR,   [Severity] CHAR,   [CVE] CHAR,
>> [Ticket] INTEGER);
>>
>> There's only 2049 results in TWScan, so I'm not concerned about speed, and
>> there is no direct relationship between the two tables.  That is the
>> entire
>> DDL for the entire database.
>>
>> What I want to do is use SkipRemed to "Filter Out" or "Filter For" results
>> in TWScan based on the Remediation table.  The difference between Out and
>> For would be just in the calling query.  Right now, I want to filter OUT
>> results in SkipRemed so I can more easily see what reports are commonly
>> themed and result in one action being required to correct.  (IE: Upgrade
>> PHP or Apache).
>>
>> The query I've been messing with is this:
>>
>> *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
>> Remediation not like (select distinct Skip from SkipRemed) order by
>> upper(Desc),upper(Service)*
>>
>> I don't get the results I want, unless I use the actual full text of the
>> Remediation text.  I've changed Skip to '%'||Skip||'%' in the subquery but
>> that doesn't get me the results I want either.
>>
>> Somethings up with my logic, not sure where.  Ideas?
>>
>
> SELECT DISTINCT ExtIP, IntIP, Service, Remediation
>   FROM PMEScan
>  WHERE NOT EXISTS(SELECT 1 FROM SkipRemed WHERE Remediation LIKE Skip)
>    AND ...
>  ORDER BY ...
>
>
> Should do it.
> Cheers,
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...