Splunk Search

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

sieutruc
Contributor

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.

Tags (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

sieutruc
Contributor

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.

0 Karma

somesoni2
Revered Legend

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

0 Karma

sieutruc
Contributor

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

0 Karma

sundareshr
Legend

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

0 Karma

sieutruc
Contributor

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.

0 Karma

sieutruc
Contributor

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.

0 Karma

sundareshr
Legend

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
0 Karma

sieutruc
Contributor

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)

0 Karma

sundareshr
Legend

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
0 Karma

sieutruc
Contributor

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

0 Karma

sundareshr
Legend

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
0 Karma

sieutruc
Contributor

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.

0 Karma

sundareshr
Legend

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
0 Karma

sieutruc
Contributor

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

0 Karma

sieutruc
Contributor

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.

0 Karma

sundareshr
Legend

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

0 Karma

sieutruc
Contributor

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

0 Karma

sieutruc
Contributor

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.

0 Karma

sieutruc
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...