Splunk Search

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

DjNaGuRo
Explorer

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)

Labels (2)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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 ]

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

It works. Thanks!

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

 

0 Karma

DjNaGuRo
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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]
0 Karma

DjNaGuRo
Explorer

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

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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 ]

View solution in original post

DjNaGuRo
Explorer

It's okay Thanks!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
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 ]
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!