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 <[email protected]> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
