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

[HattrickNZ]

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

[somesoni2 · Accepted solution]

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+)\)"

[HattrickNZ]

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?

[somesoni2]

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.

[HattrickNZ]

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

name1
1   
907473350
907473351
907473352
2   
907466983
907466984
907466985
907466986
907466987
907466988
907466989
907466990
907466991
907466992
907466993
907466994
907466995
907466996
3   
907466966
907466967
907466968
907466969
907466970
907466971
907466972
907466973
907466974
907466975
907466976
907466977
907466978
907466979
...

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.

[somesoni2]

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

[HattrickNZ]

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

[somesoni2]

just use this after rex.

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

[HattrickNZ]

thank you!

[MuS]

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

[HattrickNZ]

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.

1

name1
1907466966
1907466967
1907466968
...
1907466969

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