Splunk Search

Using Regex to split a field?

anandhalagaras1
Communicator

Hi Team,

I have sample set of events coming from the same logs and here "x" denotes a digit mostly IP address in this case and my requirement is that to split the data in the existing field "Forwarder"  which is mentioned as "v". So already we have a field extraction in place i.e. the name of field is "Forwarder". And the current output is as below from all the 5 sample events and for the 5th sample event we don't have the "vvv" value itself in the logs.

Sample Logs:

2021-02-12 06:23:17 xx.xxx.xxx.xx GET /test/v1/xyz/abc/domainsetting domainName=xx.xxx.xxx.xx 443 - xx.xxx.x.xxx function/xxx.x.x.x - xxx x x xx vv.vvv.vvv.vv

2021-02-12 06:23:26 xx.xxx.xxx.xx GET /Window-2020-def-yy-ab - 443 - xx.xxx.x.xxx Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Linuxx/5.0;+AppInfo) - xxx x x xxx vv.vv.v.v

2021-02-12 06:11:55 xx.xxx.xxx.xx POST /test/abc/api/Control/Match - 443 - xx.xxx.x.xxx Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/88.0.4324.150+Safari/537.36 https://abc.def-mm.com/abc/def/dashboard/DeliveryList/DeliveryDetail?deliveryId=xxxxx&deliverySource... xxx x x xxx vvv.vv.vvv.vvv,+vv.vvv.v.vvv,+vv.vvv.vvv.vv

2021-02-12 01:14:47 xx.xxx.xxx.xx GET /test/Abcdefgh/login+button+with+xyz.jpg - 443 - xx.xxx.x.xxx Mozilla/5.0+(iPhone;+CPU+iPhone+OS+14_4+like+Mac+OS+X)+AppleWebKit/605.1.15+(KHTML,+like+Gecko)+Mobile/xxxxx - xxx x x x vvvv:vvvv:vvvv:vvvv:vvvv:vvvv:vvvv:vvvv,+vvv.vvv.vvv.vvv

2021-02-12 07:32:20 xx.xxx.xxx.xx GET / - 443 - xx.xxx.x.xx - - x xx x x -

 

Forwarder (field name)

vv.vvv.vvv.vv
vv.vv.v.v
vvv.vv.vvv.vvv,+vv.vvv.v.vvv,+vv.vvv.vvv.vv
vvvv:vvvv:vvvv:vvvv:vvvv:vvvv:vvvv:vvvv,+vvv.vvv.vvv.vvv

 

So I want to split them up in the same field name "Forwarder" i.e. Consider the 3rd (vvv.vv.vvv.vvv,+vv.vvv.v.vvv,+vv.vvv.vvv.vv) & 4th output (vvvv:vvvv:vvvv:vvvv:vvvv:vvvv:vvvv:vvvv,+vvv.vvv.vvv.vvv) in which the "Forwarder" field has multiple IP's in the same field for the 3rd and 4th output so we want to extract in separate fields under "Forwarder" with each IP.

Eg:
Forwarder (field name)
123.456.78.912,+45.675.3.123,+32.123.456.55

Output should be as below for Forwarder Field name as:
123.456.78.912
45.675.3.123
32.123.456.55

So kindly help me with the regex for the same.

Labels (1)
0 Karma
1 Solution

scelikok
SplunkTrust
SplunkTrust

You may have Forwarder field with empty value. You can filter them before makemv command;

index=abc sourcetype=def
| search Forwarder!=""
| makemv Forwarder delim=","
| mvexpand Forwarder
| eval Forwarder=replace(Forwarder, "\+", "")
| stats count by Forwarder
If this reply helps you an upvote and "Accept as Solution" is appreciated.

View solution in original post

karthikvj
Loves-to-Learn

If you could get the Forwarder field contains value: "123.456.78.912,+45.675.3.123,+32.123.456.55"

 

| rex field=forwarder "(?<numbers>\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" max_match=3

max_match parameter should be maximum number since forwarder might have more than 10 values, depending on your use case.Screenshot 2023-01-19 201709.png

0 Karma

scelikok
SplunkTrust
SplunkTrust

You may have Forwarder field with empty value. You can filter them before makemv command;

index=abc sourcetype=def
| search Forwarder!=""
| makemv Forwarder delim=","
| mvexpand Forwarder
| eval Forwarder=replace(Forwarder, "\+", "")
| stats count by Forwarder
If this reply helps you an upvote and "Accept as Solution" is appreciated.

anandhalagaras1
Communicator

@scelikok ,

Thank you it worked like a charm.

0 Karma

scelikok
SplunkTrust
SplunkTrust

Hi @anandhalagaras1,

@saravanan90 solution should be working but I think there is misunderstanding with the sample eval. You don't need to write your ip addresses, they should come from your search already. 

In your question you told us Forwarder field has ip addresses, please try adding below in your search. 

| makemv Forwarder delim="," 
| mvexpand Forwarder 
| eval Forwarder=replace(Forwarder, "\+", "") 
| stats count by Forwarder
If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

anandhalagaras1
Communicator

@scelikok ,

Thanks for your response.

So now I have ran the query as you have mentioned:

index=abc sourcetype=def
| makemv Forwarder delim=","
| mvexpand Forwarder
| eval Forwarder=replace(Forwarder, "\+", "")
| stats count by Forwarder

And after which the first field is blank and I can see huge number of count and for the rest of the field I can see IP's split up with count.

So why the first field is blank with no information has so much of count whereas the rest has the IP and count.

Sample output:

Forwarder                 Count

                                       4500

123.456.78.432        23

345.342.543.123       12

 

0 Karma

saravanan90
Contributor

Please check if the below query helps.

|makeresults count=10 | eval forwarder="123.456.78.912,45.675.3.122,32.123.456.54,12.45.78.912,45.675.3.122,32.123.456.54" |makemv forwarder delim="," | mvexpand forwarder | stats count by forwarder

0 Karma

anandhalagaras1
Communicator

@saravanan90 

Thank you for your response.

So instead of mentioning all the IP's in  eval Forwarder part in the query can we mention something like * since there are multiple number of IP's so we cant able to mention all of them. Also for another set of sourcetype we have the Forwarder field extracted as well. So can we include the index and sourcetype as well in the same query so that it will be unique as well.  Hence Kindly help on it.

 

0 Karma

saravanan90
Contributor

Yes. We need to add the base search with index, sourcetype & other filters to extract the events and get the forwarder ip value as mentioned. 

Eg:
Forwarder (field name)
123.456.78.912,+45.675.3.123,+32.123.456.55 

0 Karma

anandhalagaras1
Communicator

Hi Saravanan,

Thank you for your response.

 

But in few cases we have more than 30+ IP's so for each and everything how can we enter the IP in the Forwarder field to search the logs instead can we use something like * i.e. It can be whatever might be the IP but it needs to be arranged properly in the Forwarder field.

Example:

123.456.789

123.456.789,+321.345.456.432,+987.654.321.123,+875.453.23.345,+.......

 

So I want the output to be as below :

Forwarder                      Count

123.456.789                      2

321.345.456.432             1

987.654.321.123             1

875.453.23.345                1

And also the count is restricted to 10 but we want all the values split up as above. So kindly help with the query with split up along with count. 

 

0 Karma

anandhalagaras1
Communicator

Can anyone help me regarding my requirement with field split up.

0 Karma

saravanan90
Contributor

This may help...

|makeresults | eval forwarder="123.456.78.912,+45.675.3.123,+32.123.456.55" | makemv delim="," forwarder

(or)

|makeresults | eval forwarder="123.456.78.912,+45.675.3.123,+32.123.456.55" | rex field=forwarder max_match=0 "(?<Forwarder>[^\,]+)"

0 Karma

anandhalagaras1
Communicator

Thank you for your response. 

But in your search query you have provided the example IP which I have mentioned but in similar type we have so many IP's and moreover I want to use the command and sort the details as well in the query.

index=abc sourcetype=xyz* | stats count by Forwarder | sort -count 

So i want to split the data and their count as well. So we can work on our exact requirement. So kindly help on the query.

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 ...