topic Re: How to use extracted field as input parameter to another search? in Splunk Search

How to use extracted field as input parameter to another search?

rahulkawadkar26 — Wed, 12 Jun 2019 11:03:35 GMT

Hi,

I needed help with using field extracted in the search(ORG) to be used as input for another search where a similar field is present(ORGFROMINVENTORY).
If these two fields match mark them as 'OK' and if these fields do not match mark them as 'NOK'.

(index=teamcity source="ORGINVENTORY") OR (index=jenkins source="ORGINVENTORY")
| rex field=_raw "(?ms)^(?:[^;\n]*;){6}(?P<ORGANIZATIONINVENTORY >[^;]+)" offset_field=_extracted_fields_bounds
| dedup ORGANIZATIONINVENTORY 
| append [ search (index=* OR index=_) index=teamcity sourcetype="teamcity:vcs" jetbrains.buildServer.VCS 
| rex field=_raw "(?ms)^(?:[^\"\\n]\"){3}(?P<vcsRoot>[^\"]+)" offset_field=_extracted_fields_bounds 
| search vcsRoot=*git* 
| dedup vcsRoot 
| eval connectionType = case(like(vcsRoot, "git@%"),"ssh", like(vcsRoot, "http%"),"https") 
| eval customSSH=case(connectionType=="ssh",'vcsRoot') ,customHTTP=case(connectionType=="https",'vcsRoot')
| makemv delim="/" customHTTP 
| makemv delim=":" customSSH 
| eval customSSH=mvindex(customSSH,1) 
| makemv delim="/" customSSH 
| eval ORG=case(connectionType=="https",mvindex(customHTTP,2),connectionType=="ssh",mvindex(customSSH,0))
| dedup ORG
] | eval suspicious = if(match(ORGANIZATIONINVENTORY ,ORG), "No", "Yes")
| table ORG, ORGANIZATIONINVENTORY , suspicious

I'm unable to match the field extracted in first query(ORG) with the field in second query(ORGFROMINVENTORY). Is there any function that I can make use of?
Is there any way I can pass the field extracted in first query to lookup for a similar value in second search?
I can't seem to get ORGFROMINVENTORY in my search result.

Can you point out the mistake i'm making. I'm getting ORGANIZATIONINVENTORY value null in my search result

ORG ORGANIZATIONINVENTORY suspicious
ORG1 Yes
ORG2 Yes
ORG3 Yes
ORG4 Yes

Re: How to use extracted field as input parameter to another search?

FrankVl — Wed, 12 Jun 2019 12:27:26 GMT

Can you please update your answer with the correct search query posted as code. I tried fixing it, but seems some of your code contains characters like <> which get filtered if you don't post them as code.

Overall this approach cannot work. You can't just take 2 lists and compare them side-by-side like this, you will need some common field to link the related rows from the 2 lists together. Without such a linking pin, the best you could do is see if the values from one result occur in the other. Is that what you want?

There are also various issues with your search when looking at the details:
| stats list(ORG) as "Organizations extracted from TeamCity" returns a single (multivalued) row.
You then append a bunch of additional rows from the 'inventory', but those are on separate rows, so that will not allow comparison.
Also: the field names in your eval do not match what your fields are actually called.

But before we go into fixing it, please clarify what exactly you want to achieve and please update your question with the correct search code.

Re: How to use extracted field as input parameter to another search?

rahulkawadkar26 — Wed, 12 Jun 2019 12:43:54 GMT

I want to get each value from ORGINVENTORY extracted in first search and validate it against results obtained from second search query where i'm deriving a similar value(ORG). If they match flag them as OK and if they do not match flag them as NOK.
I could do with verifying if one result occur in another.
Also, I'd like to know about this 'linking-pin' suggestion, any pointers on how it can be achieved?

Any sort of suggestion is welcome, I'm fairly new to splunk and do understand, my approach may not be the best.

Re: How to use extracted field as input parameter to another search?

FrankVl — Wed, 12 Jun 2019 13:14:01 GMT

Assuming your search code is correct (apart from the actual matching you were trying to do), I think the following should work:

(index=teamcity source="ORGINVENTORY") OR (index=jenkins source="ORGINVENTORY")
 | rex field=_raw "(?ms)^(?:[^;\n]*;){6}(?P<ORGANIZATIONINVENTORY >[^;]+)" offset_field=_extracted_fields_bounds
 | dedup ORGANIZATIONINVENTORY 
 | append [ search (index=* OR index=_) index=teamcity sourcetype="teamcity:vcs" jetbrains.buildServer.VCS 
 | rex field=_raw "(?ms)^(?:[^\"\\n]\"){3}(?P<vcsRoot>[^\"]+)" offset_field=_extracted_fields_bounds 
 | search vcsRoot=*git* 
 | dedup vcsRoot 
 | eval connectionType = case(like(vcsRoot, "git@%"),"ssh", like(vcsRoot, "http%"),"https") 
 | eval customSSH=case(connectionType=="ssh",'vcsRoot') ,customHTTP=case(connectionType=="https",'vcsRoot')
 | makemv delim="/" customHTTP 
 | makemv delim=":" customSSH 
 | eval customSSH=mvindex(customSSH,1) 
 | makemv delim="/" customSSH 
 | eval ORG=case(connectionType=="https",mvindex(customHTTP,2),connectionType=="ssh",mvindex(customSSH,0))
 | dedup ORG
 ] 
 | eventstats values(ORGANIZATIONINVENTORY) as ORGANIZATIONINVENTORY
 | search ORG=*
 | eval suspicious = if(ORG=ORGANIZATIONINVENTORY, "No", "Yes")
 | table ORG, ORGANIZATIONINVENTORY , suspicious

After the base search and append [] part I copied from your search, you basically have rows with ORGANIZATIONINVENTORY field, followed by rows with ORG field. (try running only that part of the search to see for yourself).
Then with eventstats values(ORGANIZATIONINVENTORY) as ORGANIZATIONINVENTORY I add a multivalued list of all occuring ORGANIZATIONINVENTORY field values to each row.
Then I search for only the rows with ORG field, as those are the rows which you want to check against the list in ORGANIZATIONINVENTORY.
Then do the eval. No need to use match(), just an = comparison of the ORG value against the multivalued ORGANIZATIONINVENTORY field will work.

Re: How to use extracted field as input parameter to another search?

rahulkawadkar26 — Wed, 12 Jun 2019 13:58:12 GMT

Thanks, the output seems way out of order so I removed 'ORGANIZATIONINVENTORY ' field in the table.
It is the same output that i desired. Thanks again for your inputs. 🙂

Re: How to use extracted field as input parameter to another search?

FrankVl — Wed, 12 Jun 2019 14:08:33 GMT

Yeah, as mentioned that field contains the full list of values to check against, so probably indeed better to not include that in the output 🙂