topic Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B) in Splunk Search

HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

DjNaGuRo — Mon, 17 May 2021 10:20:25 GMT

Hello everyone,

I'm new in Splunk. My issue is to make an EXCEPT SQL query in SPL. Something like the following:

index="trainning" sourcetype="userList" | rex field=userId "\w(?<codeId>\w+)" | WHERE NOT codeId IN [ search index="trainning" sourcetype="adminUserList" | table adminId] | table userId userName userProfile

The problem it's that the subsearch doesn't return its result in appropriated format as ("adminid1", "adminid2", ..., "adminidN").

Thanks in advance for your answers and solutions.

Sorry, I modified my question to take into account the real SPL query issue (I wasn't in front of my Pro PC last time)

Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

ITWhisperer — Sat, 15 May 2021 11:52:59 GMT

Will something like this work?

index="trainning" sourcetype="userList" userId userName userProfile | WHERE NOT [ search index="trainning" sourcetype="adminUserList" | table adminId | rename adminId as userId]

Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

DjNaGuRo — Sat, 15 May 2021 16:28:57 GMT

It works. Thanks!

index="training"
| WHERE NOT [ search index="training"
| table adminId | rename adminId as userId]
| table userId userName userProfile

Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

DjNaGuRo — Mon, 17 May 2021 07:53:29 GMT

Sorry @ITWhisperer, I modified the question and your solution doesn't match. Before the WHERE clause I've a process on data.

Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

ITWhisperer — Mon, 17 May 2021 08:41:59 GMT

The essence of the solution is the same - the sub-search returns rows of field=value which are OR'd then the NOT is applied

index="trainning" sourcetype="userList" userId userName userProfile | rex field=userId "\w(?<codeId>\w+)" | WHERE NOT [ search index="trainning" sourcetype="adminUserList" | table adminId | rename adminId as codeId ]

Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

ITWhisperer — Mon, 17 May 2021 09:36:18 GMT

Another thing you could try is:

index="trainning" sourcetype="userList" userId userName userProfile | rex field=userId "\w(?<codeId>\w+)" | WHERE NOT codeId IN [ search index="trainning" sourcetype="adminUserList" | table adminId | return $adminId]

Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

DjNaGuRo — Mon, 17 May 2021 10:37:48 GMT

I have the following error when I run the query you proposed :

Error in 'where' command: Type checking failed. 'XOR' only takes boolean arguments.

Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

DjNaGuRo — Mon, 17 May 2021 10:33:08 GMT

It's okay Thanks!

Re: HOW TO MAKE EXCEPT SQL QUERY IN SPL (A\B)

ITWhisperer — Mon, 17 May 2021 10:34:57 GMT

index="trainning" sourcetype="userList" userId userName userProfile | rex field=userId "\w(?<codeId>\w+)" | search NOT [ search index="trainning" sourcetype="adminUserList" | table adminId | rename adminId as codeId ]