![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
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
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
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.
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
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.
How many rows are there in the lookup? Do you have option to change the format of lookup?
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
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
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
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.
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.