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

[DjNaGuRo]

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)

[ITWhisperer]

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]

[DjNaGuRo]

It works. Thanks!

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

[DjNaGuRo]

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

[ITWhisperer]

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]

[DjNaGuRo]

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

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

[ITWhisperer · Accepted solution]

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 ]

[DjNaGuRo]

It's okay Thanks!

[ITWhisperer]

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 ]