topic Re: How to generate a search that will match an extracted field with a column name in my CSV lookup? in Splunk Search

How to generate a search that will match an extracted field with a column name in my CSV lookup?

vrmandadi — Fri, 20 Jan 2017 21:34:05 GMT

![alt text][1]Hello Splunkers,

I have a search which has some extracted fields and I am trying to match one of the extracted field with a column name in CSV and extracting other data

sourcetype=router_logs 
|rex "\[[^\"]+\]\s.\"(?(?\S+)\s(?\S+))[^\"]+\"\s(?\d+)\s(?RTR_HTTP_Other>\d+).+x_forwarded_for:.\"(?[\d\.]+).+vcap_request_id:(?\S+)\sresponse_time:(?\S+)"

Lookup:

abc.csv (attached)

I am trying to do a search which will give me the output with RTR_Call and the value of the response code(Good,Bad) along with response code(200,401,402 etc).

Thanks in advance

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

woodcock — Fri, 20 Jan 2017 21:51:25 GMT

First, make your csv a lookup by going to Settings -> Lookups -> Lookup Definitions -> New and saving as, for example, MyNewLookup.
Then search like this:

sourcetype=router_logs |rex "\[[^\"]+\]\s.\"(?<RTR_Call>(?<RTR_Method>\S+)\s(?<RTR_HTTP_Path>\S+))[^\"]+\"\s(?<RTR_HTTP_Resp>\d+)\s(?<RTR_HTTP_Other>\d+).+x_forwarded_for:.\"(?<RTR_X_Forwarded_For>[\d\.]+).+vcap_request_id:(?<vcap_request>\S+)\sresponse_time:(?<RTR_Resp_Time>\S+)" | lookup MyNewLookup RTR_Call

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

somesoni2 — Fri, 20 Jan 2017 21:52:26 GMT

How many rows are there in the lookup? Do you have option to change the format of lookup?

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

vrmandadi — Fri, 20 Jan 2017 23:42:19 GMT

if we get the result then there would be no problem changing it,there are 592 rows,Do you have any suggestions to change the lookup

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

vrmandadi — Fri, 20 Jan 2017 23:43:20 GMT

I have added already added it as a lookup,but I am trying to match with a common field and get the reposne codes of it

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

woodcock — Sat, 21 Jan 2017 01:22:27 GMT

This lookup should pull in all fields, is it? Is the problem is that you are not interested in fields called 400, etc. but in a single field called error that has a value of 400? If so, you need to write a search (which we can help you do) to convert the format of your csv so that the lookup will pull in correctly.

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

vrmandadi — Tue, 29 Sep 2020 12:30:47 GMT

Initially we were calculating the Good and bad by doing an eval command.

| eval isgood=if(RTR_HTTP_Resp<300,1,0) .But now we want to populate good or bad of the response codes from the lookup for each RTR_Call.

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

somesoni2 — Sun, 22 Jan 2017 20:10:56 GMT

I don't see the attached file now but based on what I remember of your lookup table, instead of below format:

RTR_Call, someother field,200,302,40...all other http response codes as column
xyz, abc,good,good,bad...all other http response values

, you need to convert it to a simpler lookup format as

RTR_Call, someother field,response_code value
xyz,abc,200,good
xyz,abc,200,good

So that you can do a simple lookup as (check the field names)

sourcetype=router_logs | rex 
"\[[^\"]+\]\s.\"(?<RTR_Call>(?<RTR_Method>\S+)\s(?<RTR_HTTP_Path>\S+))[^\"]+\"\s(?<RTR_HTTP_Resp>\d+)\s(?<RTR_HTTP_Other>\d+).+x_forwarded_for:.\"(?<RTR_X_Forwarded_For>[\d\.]+).+vcap_request_id:(?<vcap_request>\S+)\sresponse_time:(?<RTR_Resp_Time>\S+)" | lookup MyNewLookup RTR_Call as RTR_Method response_code as RTR_HTTP_Resp OUTPUT value

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

vrmandadi — Tue, 29 Sep 2020 12:34:37 GMT

sorry I have attached the lookup now.

Initially we were calculating the Good and bad by doing an eval command.

| eval isgood=if(RTR_HTTP_Resp<300,1,0) .But now we want to populate good or bad of the response codes from the lookup for each RTR_Call from search

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

somesoni2 — Mon, 23 Jan 2017 19:06:19 GMT

Did you try formatting your lookup as I mentioned and tried??
Also, your lookup seems have duplicate values of RTR_Call so you might want to include more fields in lookup command to get appropriate results.

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

vrmandadi — Tue, 24 Jan 2017 16:12:30 GMT

Sorry for the confusion Somesh, the lookup format cannot be changed as it affects other searches ,so is there a way to do a lookup in csv and save the result to a variable

Re: How to generate a search that will match an extracted field with a column name in my CSV lookup?

somesoni2 — Tue, 24 Jan 2017 16:25:04 GMT

Well, you can try this in-efficient option using join command. In the join subsearch, there is a command for fields, when you need to remove all other fields which are not required, basically everything except RTR_Call and response code columns.

sourcetype=router_logs | rex 
 "\[[^\"]+\]\s.\"(?<RTR_Call>(?<RTR_Method>\S+)\s(?<RTR_HTTP_Path>\S+))[^\"]+\"\s(?<RTR_HTTP_Resp>\d+)\s(?<RTR_HTTP_Other>\d+).+x_forwarded_for:.\"(?<RTR_X_Forwarded_For>[\d\.]+).+vcap_request_id:(?<vcap_request>\S+)\sresponse_time:(?<RTR_Resp_Time>\S+)" | join type=left RTR_Method RTR_HTTP_Resp [| inputlookup yourlookup.csv | fields - field1 field2 | untable RTR_Call RTR_HTTP_Resp value ]

After removing the not required fields and running the untable command, your lookup data will be transformed into the new format that I was suggesting and you would be able to join.