topic Re: merge two events which have different fields but they have the same values in Splunk Search

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

sieutruc — Thu, 22 Sep 2016 11:26:40 GMT

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.

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

sundareshr — Thu, 22 Sep 2016 12:07:56 GMT

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

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

sieutruc — Thu, 22 Sep 2016 13:01:12 GMT

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.

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

sundareshr — Thu, 22 Sep 2016 13:08:25 GMT

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

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

sieutruc — Thu, 22 Sep 2016 13:37:59 GMT

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)

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

sundareshr — Thu, 22 Sep 2016 13:48:06 GMT

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

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

sieutruc — Thu, 22 Sep 2016 14:05:49 GMT

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

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

sundareshr — Thu, 22 Sep 2016 14:10:46 GMT

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

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

sieutruc — Thu, 22 Sep 2016 14:51:54 GMT

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.

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

sundareshr — Thu, 22 Sep 2016 15:06:56 GMT

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

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

sieutruc — Thu, 22 Sep 2016 15:52:16 GMT

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.

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

sundareshr — Thu, 22 Sep 2016 16:13:46 GMT

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

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

somesoni2 — Thu, 22 Sep 2016 17:06:18 GMT

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)?

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

sieutruc — Thu, 22 Sep 2016 17:45:38 GMT

sorry for late reply, i just went back home. If i use the transaction with id , it does not give the right result.

For example, the following table would give the right result if i use the transaction with id (id gets the same value for one event)

 UID                 mid                    id
 306825245      119526183      306825245
 306825245            -        306825245
 306825245            -        306825245
 306825245            -        306825245
 971637133      306825245      306825245
 971637133           -         306825245
 971637133           -         306825245
 971637133           -         306825245

306825245 is the key to connect them (2UIDs).

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

sieutruc — Thu, 22 Sep 2016 20:48:00 GMT

Sorry for my reply, i came back home. 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 grouping (here 2 events) , the mid=306825245 is the key to group the 1st event, and mid=207825245 for the second event.

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

sieutruc — Thu, 22 Sep 2016 20:52:14 GMT

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.

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

sieutruc — Thu, 22 Sep 2016 20:52:43 GMT

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

If the table was like :

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.

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

sieutruc — Thu, 22 Sep 2016 20:53:19 GMT

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

If the table was like :

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.

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

sieutruc — Mon, 26 Sep 2016 08:06:39 GMT

@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 ?

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

sieutruc — Mon, 26 Sep 2016 08:07:34 GMT

@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 ?