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)
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 ]
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]
It works. Thanks!
index="training"
| WHERE NOT [ search index="training"
| table adminId | rename adminId as userId]
| table userId userName userProfile
Sorry @ITWhisperer, I modified the question and your solution doesn't match. Before the WHERE clause I've a process on data.
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]
I have the following error when I run the query you proposed :
Error in 'where' command: Type checking failed. 'XOR' only takes boolean arguments.
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 ]
It's okay Thanks!
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 ]