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 <slav...@bigfraud.org> wrote: > > On 19 Apr 2017, at 8:12pm, Stephen Chrzanowski <pontia...@gmail.com> > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users