merge two events which have different fields but they have the same values

[sieutruc]

Hello,

i have the following logs ( 4 events):

1)
Sep 21 15:36:11 test.infra : Info: Start UID 306825245 ICID 111270602
Sep 21 15:36:11 test.infra : Info: UID 306825245 ICID 111270602 receivedTest:
Sep 21 15:36:11 test.infra : Info: UID 306825245 ICID 111270602 RID 0 user:
Sep 21 15:36:13 Info: UID 306825245 RID [0] Response 'ok: Message 119526183 accepted'
2)
Sep 21 15:36:05 test.infra : Info: Start UID 971637133 ICID 319258725
Sep 21 15:36:05 test.infra : Info: UID 971637133 ICID 111270602 receivedTest:
Sep 21 15:36:05 test.infra : Info: UID 971637133 ICID 319258725 RID 0 user:
Sep 21 15:36:09 Info: UID 971637133 RID [0] Response 'ok: Message 306825245 accepted'
3)
Sep 21 15:34:11 test.infra : Info: Start UID 207825245 ICID 111270602
Sep 21 15:34:11 test.infra : Info: UID 207825245 ICID 111270602 receivedTest:
Sep 21 15:34:11 test.infra : Info: UID 207825245 ICID 111270602 RID 0 user:
Sep 21 15:34:13 Info: UID 207825245 RID [0] Response 'ok: Message 134526103 accepted'
4)
Sep 21 15:34:05 test.infra : Info: Start UID 187478569 ICID 319258725
Sep 21 15:34:05 test.infra : Info: UID 187478569 ICID 319258725 receivedTest:
Sep 21 15:34:05 test.infra : Info: UID 187478569 ICID 319258725 RID 0 user:
Sep 21 15:34:09 Info: UID 187478569 RID [0] Response 'ok: Message 207825245 accepted'

I wan to group them into 2 events. The event is grouped based on UID and the id from the last message ( Message 207825245 accepted'). For ex: in the second event, it has UID = 207825245 and accepted message id = 306825245. This will be grouped with the first event because the UID of the first event equals to the accepted message id of the second message.

So with that, the wesutl should be
:
1)
Sep 21 15:36:11 test.infra : Info: Start UID 306825245 ICID 111270602
Sep 21 15:36:11 test.infra : Info: UID 207825245 ICID 111270602 receivedTest:
Sep 21 15:36:11 test.infra : Info: UID 306825245 ICID 111270602 RID 0 user:
Sep 21 15:36:13 Info: UID 306825245 RID [0] Response 'ok: Message 119526183 accepted'
Sep 21 15:36:05 test.infra : Info: Start UID 971637133 ICID 319258725
Sep 21 15:36:05 test.infra : Info: UID 207825245 ICID 111270602 receivedTest:
Sep 21 15:36:05 test.infra : Info: UID 971637133 ICID 319258725 RID 0 user:
Sep 21 15:36:09 Info: UID 971637133 RID [0] Response 'ok: Message 306825245 accepted'

2)
Sep 21 15:34:11 test.infra : Info: Start UID 207825245 ICID 111270602
Sep 21 15:34:11 test.infra : Info: UID 207825245 ICID 111270602 receivedTest:
Sep 21 15:34:11 test.infra : Info: UID 207825245 ICID 111270602 RID 0 user:
Sep 21 15:34:13 Info: UID 207825245 RID [0] Response 'ok: Message 134526103 accepted'
Sep 21 15:34:05 test.infra : Info: Start UID 187478569 ICID 319258725
Sep 21 15:34:05 test.infra : Info: UID 187478569 ICID 319258725 receivedTest:
Sep 21 15:34:05 test.infra : Info: UID 187478569 ICID 319258725 RID 0 user:
Sep 21 15:34:09 Info: UID 187478569 RID [0] Response 'ok: Message 207825245 accepted'

Can someone helpe me resolve this case ? all suggestion will be appreciated.

[gcusello]

it isn't a quick search because uses two transactions but should run:

your search | rex "UID\s(?\d+)" | rex "Message\s(?\d+)" | transaction uid | transaction mid

In this way, you at first group the events with the same uid and after you group the already grouped events with the same mid.

I encountered the same problems some years ago!
Bye.
Giuseppe

[sieutruc]

Thanks for your reply, but transaction mid does not group them into 2 events as desired. The reason is each events after "transaction uid" has its own mid.

[somesoni2]

In the sample events, each line is one event (as they appear in Splunk) OR one event consists of 4 lines (like you marked here)?

[sieutruc]

Each line is one event originally. The sample i show here is after using the transaction for UID.

[sundareshr]

Try this

base search | rex "UID\s(?<uid>\d+)" | rex "Message\s(?<mid>\d+)" | eval id=if(isnull(mid), uid, mid) | sort id

This will give you a field called id that is common. You can use this to group your events to calculate stats. For example stats count by id

[sieutruc]

Thanks for your reply, but your solution does not work. The field "mid" exists in all events so the query "eval id=if(isnull(mid), uid, mid)" returns always mid => so i cannot group them.

[sieutruc]

sorry for my late reply. No, it did not give the right grouping.

If the table was like :

UID mid id
306825245 119526183 306825245
306825245 - 306825245
306825245 - 306825245
306825245 - 306825245
971637133 306825245 306825245
971637133 - 306825245
971637133 - 306825245
971637133 - 306825245
207825245 134526103 207825245
207825245 - 207825245
207825245 - 207825245
207825245 - 207825245
187478569 207825245 207825245
187478569 - 207825245
187478569 - 207825245
187478569 - 207825245

it would give the right answer. The mid=306825245 is a key to create first event, and mid=207825245 is for creating the second event.

[sundareshr]

What do you mean mid exists in all the events? What do you get for this?

base search | rex "UID\s(?<uid>\d+)" | rex "Message\s(?<mid>\d+)" 

If mid is empty for most of the fields, then try this

 base search | rex "UID\s(?<uid>\d+)" | rex "Message\s(?<mid>\d+)" | eval id=if(len(mid)<2, uid, mid) | sort id

[sieutruc]

i used

base search | rex "UID\s(?<uid>\d+)" | rex "Message\s(?<mid>\d+) accepted" 

The 1st event : UID=306825245 , mid=119526183
The 2nd event: UID=971637133 , mid=306825245 # mid of 2nd = UID of 1st
The 3rd event: UID=207825245 , mid=134526103
The 4th event: UID=187478569 , mid=207825245 # mid of 4h = UID of 3st

So your solution : eval id=if(len(mid)<2, uid, mid) | sort id does not work , it does not group to 2 events from 4 events : (1+2), (3+4)

[sundareshr]

Try this

base search | rex field=_raw mode=sed "s/\n/|/g" | makemv _raw delim="|" | mvexpand _raw | rex "UID\s(?<uid>\d+)" | rex "Message\s(?<mid>\d+)" | eval id=if(isnull(mid), uid, mid) | sort id

[sieutruc]

i tested and it does not work. the result is always 4 events , they are not grouped into 2. What i would like to achieve is : event 1 + event 2 = 1 event (caues the mid of evt 2 = UID of event 1), similarly for event 3 and 4.

And the field id gets 4 different values for 4 events

[sundareshr]

You need to split the 4 lines in each event, to separate/individual events. Ideally, you should index it that way. If that is an option, I would recommend reindexing your data where each line is an event. Then my original query will work. This the last thing I can think of trying, not sure it will work, but worth a try

base search | eval x=_raw | rex field=x mode=sed "s/\n/|/g" | makemv x delim="|" | mvexpand x | table x | rex field=x "UID\s(?<uid>\d+)" | rex field=x "Message\s(?<mid>\d+)" | eval id=if(isnull(mid), uid, mid) | sort id

[sieutruc]

Actually, the original events are line by line. I used the transaction to group them basing on the UID. But after that, i got this problem and do not how to solve.

I tried your commands with the original events. it does not change. The difficulty is how to group 2 transactions with 2 UID to only one transaction like described in my post.

[sundareshr]

What do you get when you run this without the transaction command?

base search | rex "UID\s(?<uid>\d+)" | rex "Message\s(?<mid>\d+)" | table uid mid

[sieutruc]

@sundareshr : i cannot reply to your comment. The forum deleted aumatically my reply everytime. It seems a bug of the splunk web.

Can you take a look at my reply below ?

[sieutruc]

the table is :

UID                 mid                    id
306825245      119526183      119526183
306825245            -        306825245
306825245            -        306825245
306825245            -        306825245
971637133      306825245      306825245
971637133           -         971637133
971637133           -          971637133
971637133           -          971637133
207825245       134526103       134526103
207825245           -         207825245
207825245           -         207825245
207825245           -         207825245
187478569       207825245       207825245
187478569           -         187478569
187478569           -         187478569
187478569           -         187478569

You see the field "id" does not have 2 separate values.

[sundareshr]

If you use the id field for the transaction command, it will not give you the right grouping?

[sieutruc]

@sundareshr : i cannot reply to your comment. The forum deleted aumatically my reply everytime. It seems a bug of the splunk web.

Can you take a look at my reply below ?

[sieutruc]

sorry for my late reply. No, it did not give the right grouping.

If the table was like :

UID mid id
306825245 119526183 306825245
306825245 - 306825245
306825245 - 306825245
306825245 - 306825245
971637133 306825245 306825245
971637133 - 306825245
971637133 - 306825245
971637133 - 306825245
207825245 134526103 207825245
207825245 - 207825245
207825245 - 207825245
207825245 - 207825245
187478569 207825245 207825245
187478569 - 207825245
187478569 - 207825245
187478569 - 207825245

it would give the right answer. The mid=306825245 is a key to create first event, and mid=207825245 is for creating the second event.

[sieutruc]

sorry for my late reply. No, it did not give the right grouping.

If the table was like :

UID mid id
306825245 119526183 306825245
306825245 - 306825245
306825245 - 306825245
306825245 - 306825245
971637133 306825245 306825245
971637133 - 306825245
971637133 - 306825245
971637133 - 306825245
207825245 134526103 207825245
207825245 - 207825245
207825245 - 207825245
207825245 - 207825245
187478569 207825245 207825245
187478569 - 207825245
187478569 - 207825245
187478569 - 207825245

it would give the right answer. The mid=306825245 is a key to create first event, and mid=207825245 is for creating the second event.