Find knowledge Objects that are using sourcetypes

[vamsigurram]

I need to find the users that are using sourcetypes in their savedsearches (reports/dashboards).

I have list of sourcetypes in csv file.

SPL1:(this gives me source type list)

| inputlookup sourcetypelist.csv  

SPL2: (this gives list of savedsearches and their search string used). I see 1200 rows here.

| rest /servicesNS/-/search/saved/searches | search search="*sourcetype*"
| fields qualifiedSearch search title author

I need to combine the above 2 SPL's (inner join, append, sub search. I am not sure), to find only those saved seaches that are using the specfic sourcetypes (listed from SPL1, above.), in their savedsearch SPL's,  

| rest /servicesNS/-/search/saved/searches | search search="*sourcetype*"
| fields qualifiedSearch search title author | where match(search,"osma")

As seen highlighted above match   function (osma is one of the sourcetype value) takes string/regex, but not variable. I cannot do this | where match(search, $sourcetype_variable$)

I would appreciate if someone can help me here.

[richgalloway]

See if this helps

| rest /servicesNS/-/search/saved/searches 
| search search="*sourcetype*"
| fields qualifiedSearch search title author 
| rex field=qualifiedSearch "sourcetype\s*=\s*(?<st>[\w\*]+)"
| lookup sourcetypelist.csv st as sourcetype OUTPUT sourcetype as sourcetypefound
| where isnotnull(sourcetypefound)

[vamsigurram · Accepted solution]

I found the issue in the lookup.

This is working.

| rest /servicesNS/-/-/saved/searches
| search search="*sourcetype=*"
| fields qualifiedSearch search title author
| rex field=qualifiedSearch "sourcetype=\s*\"*(?<st>[^\"\ \)]+)"
| eval st = lower(st)
| lookup temp_pvsi_sourcetypes.csv sourcetype as st OUTPUT sourcetype as sourcetypefound
| where isnotnull(sourcetypefound)

[vamsigurram]

I updated rex. But Lookup is giving issue.

| rest /servicesNS/-/search/saved/searches
| search search="*sourcetype=*"
| fields qualifiedSearch search title author
| rex field=qualifiedSearch "sourcetype=\s*\"*(?<st>[^\"\ \)]+)"
| eval st = lower(st)
| lookup sourcetypelist.csv st as sourcetype OUTPUT sourcetype as sourcetypefound
| where isnotnull(sourcetypefound)

Error in 'lookup' command: Could not construct lookup 'sourcetypelist.csv, st, as, sourcetype, OUTPUT, sourcetype, as, sourcetypefound'. See search.log for more details.

| inputlookup temp_pvsi_sourcetypes.csv (this gives fields index, sourcetype)