topic How to join search and use join ? in Splunk Search

How to join search and use join ?

hrishi_deshpand — Mon, 18 Oct 2021 17:05:12 GMT

First Event

INFO | 2021-10-18 05:17 AM | BUSINESS RULE | Payload for ID#: 40658606156551247672591634534230307 with status Approved is published

Second Event

msg: INFO | 2021-10-14 10:38 PM | Message consumed: {"InputAmountToCredit":"22.67","CurrencyCode":"AUD","Buid":"1401","OrderNumber":"877118406","ID":"58916"}

I want to have sum of InputAmountToCredit based on status . status can vary to different statuses and ID is common field for both the events what is best way to sum the amount with the same status for specified timeframe

Thanks for all the support.

Re: How to join search and use join ?

richgalloway — Mon, 18 Oct 2021 18:27:38 GMT

Assuming the fields are extracted already, you can use the stats command to group results by a shared field.

... | stats values(*) as * by ID

Re: How to join search and use join ?

hrishi_deshpand — Fri, 22 Oct 2021 12:31:36 GMT

index = pcf_logs cf_org_name = creorg OR cf_org_name = SvcITDnFAppsOrg cf_app_name=VerifyReviewConsumerService host="*" | eval _raw = msg | rex "InputAmountToCredit\"\:\"(?<PayloadAmount>[^\"]+)" | rex "Request\#\:\s*(?<ID1>\d+) with (?<Status>\w+.\w+)" | rex "CRERequestId\"\:\"(?<ID2>[^\"]+)" | eval ID=coalesce(ID1,ID2)|stats latest(Status) as Status values(PayloadAmount) as Amount by ID

I am using this to generate report I want but as per my understanding I should create a summary index by using

| stats values(*) as * by ID

and then by using stats result I should be able to do chart or another stats or whatever. Just because

| stats values(*) as * by ID

this gives me all the data related to ID and I can play with it the way I want after creating the index. Is my understanding is correct?

Re: How to join search and use join ?

richgalloway — Fri, 22 Oct 2021 17:50:03 GMT

Yes, your understanding is correct. Be aware, however, that the values() function may produce a multi-value field. Some commands you might use after stats may not handle multi-value commands well (or at all) so additional massaging of the data may be necessary.