topic Re: How to pass values from CSV file to the main search in Getting Data In

How to pass values from CSV file to the main search

sabari80 — Wed, 13 Dec 2023 17:27:27 GMT

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.

Re: How to pass values from CSV file to the main search

isoutamo — Wed, 13 Dec 2023 17:32:10 GMT

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

Re: How to pass values from CSV file to the main search

sabari80 — Wed, 13 Dec 2023 17:58:20 GMT

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

Re: How to pass values from CSV file to the main search

dtburrows3 — Wed, 13 Dec 2023 18:13:22 GMT

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

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:

Re: How to pass values from CSV file to the main search

bowesmana — Wed, 13 Dec 2023 21:47:02 GMT

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.