topic Re: How do I use my regular expression to extract values in a column? in Splunk Search

How do I use my regular expression to extract values in a column?

HattrickNZ — Mon, 15 Feb 2016 21:57:05 GMT

I have the following search ... | stats dc() | transpose | which gives me this:

column  row 1
dc(ID)  273
dc(SBC) 2
dc(TGID)    273
dc(TGN) 504
dc(beginTime)   2
dc(c1907466966) 54
dc(c1907466967) 59
dc(c1907466968) 55
dc(c1907466969) 31
dc(c1907466970) 50
dc(c1907466971) 55
dc(c1907466972) 7
dc(c1907466973) 47
dc(c1907466974) 57

What I want is to do a regex on this column and extract the values. This is the pattern I am interested in dc(c1907466966) and this is what I want to extract 907466966 and store it in a field called name1. This is the regex I need dc\Dc(?P\d+)\D, but I can't apply it to extract that data I want to a field.

How do I do this?

This is my regex example
https://regex101.com/r/qO8aS6/1

Re: How do I use my regular expression to extract values in a column?

MuS — Mon, 15 Feb 2016 22:07:33 GMT

What do you mean with but I can't apply it to extract that data I want to a field ?

If you run this:

| stats count | eval foo="column    row 1
 dc(ID)    273
 dc(SBC)    2
 dc(TGID)    273
 dc(TGN)    504
 dc(beginTime)    2
 dc(c1907466966)    54
 dc(c1907466967)    59
 dc(c1907466968)    55
 dc(c1907466969)    31
 dc(c1907466970)    50
 dc(c1907466971)    55
 dc(c1907466972)    7
 dc(c1907466973)    47
 dc(c1907466974)    57" | rex max_match=0 field=foo "dc\Dc(?<name1>\d+)\D" | table name1

It will create a multi value field called name1.

cheers, MuS

Re: How do I use my regular expression to extract values in a column?

somesoni2 — Mon, 15 Feb 2016 22:29:48 GMT

Try something like this

To extract 907466968 from dc(c1907466968)

your base search | stats dc(*) | transpose  | rex field=column "dc\(c\d(?P<name1>\d+)\)"

To extract 1907466968 from dc(c1907466968)

your base search | stats dc(*) | transpose  | rex field=column "dc\(c(?P<name1>\d+)\)"

Re: How do I use my regular expression to extract values in a column?

HattrickNZ — Mon, 15 Feb 2016 23:19:42 GMT

tks, that something for me to work with.

... | stats dc() | transpose | rex field=column "dc\Dc(?P<name1>\d+)\D"

gives me this(the values I want are in name1 column):

column  row 1   name1
dc(ID)  273  
dc(SBC) 2    
dc(TGID)    273  
dc(TGN) 434  
dc(beginTime)   2    
dc(c1907466966) 35  907466966
dc(c1907466967) 39  907466967
dc(c1907466968) 37  907466968
dc(c1907466969) 21  907466969
dc(c1907466970) 34  907466970
dc(c1907466971) 37  907466971
dc(c1907466972) 9   907466972
dc(c1907466973) 32  907466973
dc(c1907466974) 38  907466974
dc(c1907466975) 36  907466975

But I was hoping there would fe a field value called name1 viewable in the Events tab but I cannot see it there. Can I get it to be there or should it be there?

Re: How do I use my regular expression to extract values in a column?

HattrickNZ — Mon, 15 Feb 2016 23:23:55 GMT

I want to apply the regex to the values in the column called column so I will end up with the values in one field, name1 like this.

when I say field I mean creating a field where I can do a stats values(name1) on and that will give me something like 1 above

Re: How do I use my regular expression to extract values in a column?

somesoni2 — Mon, 15 Feb 2016 23:27:06 GMT

I believe you've field names cXXXXXXXXX in your events. So if you want to extract all the code available in the fields starting with c and available in the events tab itself along with each event, try something like this

your base search | rex max_match=0 "c\d(?<name1>\d+)"

This should give a field name1, multivalued, containing all the codes. Sample events will help you get better solution.

Re: How do I use my regular expression to extract values in a column?

HattrickNZ — Tue, 16 Feb 2016 00:57:07 GMT

that works
... | rex max_match=0 "c\d(?\d+)=" | table name1 | dedup name1 and this gives me:

But why is it doing this grouping, do you think? (3 numbers in row1...etc) I would have thought it was 1 number per row.

Re: How do I use my regular expression to extract values in a column?

somesoni2 — Tue, 16 Feb 2016 02:59:01 GMT

The field name1 is multivalued field and table and dedup command will not change its format. To get each value in separate events do this after rex.

| stats count by name1 | fields - count

Re: How do I use my regular expression to extract values in a column?

HattrickNZ — Wed, 17 Feb 2016 00:04:46 GMT

tks, think thats it. do you just use the count here to get rid of duplicates?

whil I have you, what I want is

| rex max_match=0 "c\d(?\d+)" | stats values(name1) by measInfoId

and this gives me this: (which is many values(name1) per measInfoId but all this shows on 1 row.)

    measInfoId  values(name1)
1   1907425280  907465280
907465281
907465282
907465283
907465284
...
2   1907425301  907466042
907466043
907466044
907466045
...

How do I get this to be 1 row per values(name1) like

    measInfoId  values(name1)
1   1907425280  907465280
2                          907465281
3                          907465282
4                          907465283
5                           907465284
...
10  1907425301  907466042
11                                907466043
...

Re: How do I use my regular expression to extract values in a column?

somesoni2 — Wed, 17 Feb 2016 01:18:31 GMT

just use this after rex.

...| stats count by measInfoId name1 | fields - count

Re: How do I use my regular expression to extract values in a column?

HattrickNZ — Wed, 17 Feb 2016 01:52:59 GMT

thank you!