Reporting

How to correlate events by a common field after using stats

cindygibbs_08
Communicator

Hello guys I hope you are doing great! Stay safe during these times.

 

I was wondering if it was possible to correlate events after using the stats commands.. I have encountered this issue I have to run a multisearch to keep track of all of my cx reservation codes, payment status and destinations. Because of the way this was implemented in our system, I extract the reservations codes  and the payments' status from two different indexes like so:

 

 

| multisearch
[| search index=rev
| fields rev_code, id, destination]
[| search index=pay_cx
| fields rev_code, id, pay_status]
| stats values(pay_status)as pay_status values(destination) as destination by id, rev_code

 

Which gives me the destination and payment status by id and reservation code which is important since one id can be linked to multiple reservation codes... But now I want to add to this table the category of the customer "A" "B" or "C" to get this I can use this:

 

index=cx_pers_info
| fields category, id

 

but if I run stats again or other search I get either an error or nothing..I know this is  because the events of "category"  do not have a rev_code but how can I add the category field to my previous table?

Thank you so much for the help you guys I am sending you the biggest hug

 

 

Kindly,
Cindy

Labels (1)
0 Karma
1 Solution

ITWhisperer
Legend

One way to do it is with a join.

| multisearch
[| search index=rev
| fields rev_code, id, destination]
[| search index=pay_cx
| fields rev_code, id, pay_status]
| stats values(pay_status)as pay_status values(destination) as destination by id, rev_code
| join type=left id [ search index=cx_pers_info | fields category, id ]

View solution in original post

bowesmana
Super Champion

and another way would be to

| multisearch
[| search index=rev
| fields rev_code, id, destination]
[| search index=pay_cx
| fields rev_code, id, pay_status]
[| search index=cx_pers_info
| fields category, id]
| eventstats values(category) as category by id
| stats values(pay_status)as pay_status values(destination) as destination values(category) as category by id, rev_code

which is adding your customer category and pushing that to the other events with the eventstats, but it may be less efficient than using the join, but be aware that join has potential limitations if you have a large data set in the join.

cindygibbs_08
Communicator

Than so much this is a great way to learn a new approach!!!! Thank you for your code and time it means a lot to me

0 Karma

ITWhisperer
Legend

One way to do it is with a join.

| multisearch
[| search index=rev
| fields rev_code, id, destination]
[| search index=pay_cx
| fields rev_code, id, pay_status]
| stats values(pay_status)as pay_status values(destination) as destination by id, rev_code
| join type=left id [ search index=cx_pers_info | fields category, id ]

View solution in original post

cindygibbs_08
Communicator

@ITWhisperer  I tried and it worked I thought join was not even part of this splunk program thank you for being my rock! Thank you for being my teacher thank you for being so kind my dear gentleman

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!