Splunk Search

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

vrmandadi
Builder

![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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

vrmandadi
Builder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

vrmandadi
Builder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

vrmandadi
Builder

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma

vrmandadi
Builder

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

vrmandadi
Builder

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...