How to pass values from CSV file to the main search

[sabari80]

I have this search query and working fine.

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}" | stats count by user_actions | spath output=pp_user_action_application input=user_actions path=application | where pp_user_action_application="test" | spath output=pp_user_action_name input=user_actions path=name
| where pp_user_action_name in ("test.aspx")
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime | eval pp_user_action_name=substr(pp_user_action_name,0,150)
| eventstats avg(pp_user_action_response) AS "Avg_today" by pp_user_action_name
| stats count(pp_user_action_response) As "Today_Calls",perc90(pp_user_action_response) AS "Perc90_today" by pp_user_action_name Avg_today
| eval Perc90_today=round(Perc90_today/1000,2)| eval Avg_today=round(Avg_today/1000,2)
| table pp_user_action_name,Today_Calls,Avg_today,Perc90_today

PFA screenshot for the results.

Now i am trying to pass the pp_user_action_name value from the test.csv file and not getting any results 

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}" | stats count by user_actions | spath output=pp_user_action_application input=user_actions path=application | where pp_user_action_application="test" | spath output=pp_user_action_name input=user_actions path=name
| where pp_user_action_name in ([| inputlookup test.csv])
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime | eval pp_user_action_name=substr(pp_user_action_name,0,150)
| eventstats avg(pp_user_action_response) AS "Avg_today" by pp_user_action_name
| stats count(pp_user_action_response) As "Today_Calls",perc90(pp_user_action_response) AS "Perc90_today" by pp_user_action_name Avg_today
| eval Perc90_today=round(Perc90_today/1000,2)| eval Avg_today=round(Avg_today/1000,2)
| table pp_user_action_name,Today_Calls,Avg_today,Perc90_today

How to fix this? 

thanks in advance. 

[isoutamo]

Have you try to switch "where" to "search" on your SPL?

[sabari80]

Yes i have tried with Search command as well, but no luck.

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}" | stats count by user_actions | spath output=pp_user_action_application input=user_actions path=application | where pp_user_action_application="test" | spath output=pp_user_action_name input=user_actions path=name
| search pp_user_action_name in ([| inputlookup test.csv])
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime | eval pp_user_action_name=substr(pp_user_action_name,0,150)
| eventstats avg(pp_user_action_response) AS "Avg_today" by pp_user_action_name
| stats count(pp_user_action_response) As "Today_Calls",perc90(pp_user_action_response) AS "Perc90_today" by pp_user_action_name Avg_today
| eval Perc90_today=round(Perc90_today/1000,2)| eval Avg_today=round(Avg_today/1000,2)
| table pp_user_action_name,Today_Calls,Avg_today,Perc90_today

[bowesmana]

The odd thing about the IN command is that it is not consistent across search and where.

If using SEARCH you need to put the IN in capital letters, not lowercase, so technically you need to do

| search pp_user_action_name IN ...

However, as @dtburrows3 suggests, you are better off using the standard subsearch syntax for doing IN style searching, i.e.

| search [ | inputlookup test.csv 
           | fields YOUR_CSV_FIELD 
           | rename YOUR_CSV_FIELD as pp_user_action_name ]

You don't actually need the format statement as there is an implicit format statement applied to the subsearch.

[dtburrows3]

You may be better off formatting your search command to be like 

| search [ | inputlookup test.csv | fields + <filter_field> | rename <filter_field> as pp_user_action_name | format ]

You can see here that the format command formats the output of the subsearch as a valid search string that gets used in the parent search. Example of subsearch output: 

Example of this used on a local Splunk instance: