Not able to use lookup for search in SPATH command

[mohammadsharukh]

My data is coming for 0365 as JSON, I am using SPath to get the required fields after that i want to compare the data with a static list containig roles to be monitored but unforutnaly I am getting the below error
Error in 'table' command: Invalid argument: 'role="Authentication Administrator"'

Its not working. PFA the releveant snap

[PickleRick]

I'm not sure what you want to achieve but remember that subsearch will be executed first, then its output will be formatted and substituted into the main search before running it. So with the output of your subsearch the resulting table command will look like

table _time , sourceuser, targetuser, role, Operation ((role="AuthenticationAdministrator") OR (role="Authentication Policy Administrator") OR [...])

As you can see, it doesn't make sense.

[yuanliu]

The problem has nothing to do with lookup or with spath. (You are not even using lookup command.)  You cannot use a subsearch in table command.  It's that simple.

Note: Always use a code box or text to illustrate search.  Screenshot is the worst form to share data like this.

I get that you want to filter ModifiedProperties{1}.NewValue to only those roles in your subsearch.  To do this, you need search command, not table command.  Something like the following

index=o365 Operation="Add member to role."
| spath path=ModifiedProperties{1}.NewValue output=role
| search 
    [| inputlookup privileged_azure_ad_roles.csv where isprivilegedadrole=true
    | fields role]
| spath path=Actor{0}.ID output=sourceuser
| rename ObjectId as targetuser
| table _time sourceuser targetuser role Operation

Hope this helps.

[mohammadsharukh]

Adding to my problem, if i add table command then it gives error in rename command and if remove rename command then it throws error for spath command.