how to find equal values

[Mike6960]

In my data I have event which contain requests and answers for that requests
In the initial request i have a field "RequestId" In the answer the value of that field is filled in the field 'PreviousRequestId' But the answer itself also contains a field "RequestId" but then with adifferent value. How can i search on events where
"RequestId" = 'PreviousRequestId' ??
Problem is naturally that it will see a different "RequestId" in the answer
I have tried |Where "RequestId" = 'PreviousRequestId'

[DalJeanis · Accepted solution]

Okay, here are some basic things you need to know.

First, splunk's where filters events by testing conditions on a single event. It's not the same as SQL's where, which is used to filter records and to establish match keys during SQL's join.

Second, in order to marry up records, you need one or more of the various Splunk commands that are discussed in the documentation for join here. https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Join

Here is a pseudocode example. If you can do it this way, this will almost always be the most efficient way to connect up your data.

your search that gets both requests and answers
| fields _time RequestId PreviousRequestId whatever other fields you might need
| eval matchfield=if(this is a request, RequestId, PreviousRequestId)
| any other commands to keep stuff from one kind of record and get rid of stuff from the other kind
| stats min(_time) as RequestTime, max(_time) as AnswerTime, values(*) as * by matchfield

It looks like there is no PreviousRequestId on the request record, so it could be this way

your search that gets both requests and answers
| fields _time RequestId PreviousRequestId location component whatever other fields you might need
| eval matchfield=coalesce(PreviousRequestId, RequestId)
| stats min(eval(case(isnull(PreviousRequestId),date))) as RequestTime, 
    min(eval(case(isnull(PreviousRequestId),location))) as RequestLocation, 
    min(eval(case(isnull(PreviousRequestId),component))) as RequestComponent, 
    min(eval(case(isnotnull(PreviousRequestId),date))) as AnswerTime, 
    min(eval(case(isnotnull(PreviousRequestId),location))) as AnswerLocation, 
    min(eval(case(isnotnull(PreviousRequestId),component))) as AnswerComponent, 
    values(*) as * by matchfield
| fields - component location  and anything else you don't need

[Mike6960]

Thank you very much for your help, Fortunately the messageid will be unique for each set in the future. I can use that field. nevertheless, your input was very usefull for me.

[DalJeanis]

Great! Glad you got it sorted out.

[skoelpin]

Assuming your RequestId and PreviousRequestId are correct, you can do ... | search "RequestId" = "PreviousRequestId"

[Mike6960]

No, that does not work

[skoelpin]

Can you elaborate more then "it doesn't work"...

What doesn't work? Is the assumption I listed above true? If not then it won't work.

[Mike6960]

I dont really understand what you mean by 'correct' .The fields exists and there events where the value in 'PreviousRequestId' is the same as 'RequestId'

[skoelpin]

My initial assumption is asking if your fields are handling the values correctly. For this to work, you need to add some kind of state to your search which will drop the previous request id and update it with a new one when a new value comes in. To do this, you need to use streamstats and eval with an if statement. You need to tackle this task first before trying to see if the values are equal

[Mike6960]

The events can be like this (question and answer)

Initial request (question)

{ [-]
date: 2017-11-21T10:41:10,599
level: INFO
logger: XYX
message: { [-]
BusinessId: 1360158
BusinessObject: PP
CreationDate: 2017-11-21T10:41:10.599+01:00
MessageId: 1
Origin: test
RequestId: 01a7bf89-8fff-473e-a713-5f006014b087
component: CKF
location: FFBCD
}
thread: [dom_mst].domeinHttpsListener.worker.168
}

Answer:
{ [-]
date: 2017-11-21T10:41:10,875
level: INFO
logger: XYX
message: { [-]
BusinessId: 1360158
BusinessObject: PP
CreationDate: 2017-11-21T10:41:10.599+01:00
MessageId: 1
Origin: test
PreviousRequestId: 01a7bf89-8fff-473e-a713-5f006014b087
RequestId: 428aa3b9-11d7-4692-841b-388cf1914ef0
component: CF
location:FFBCA
}
thread: [dom_mst].domeinHttpsListener.worker.168
}

The fields are handling te values correctly. Anyway thank you for your contribution , I am gonna look further.

[skoelpin]

I see, you have a single event and want to see when the 2 fields match. In my opinion, you will need to break these events into individual events. This will give you the ability to see if they match

[Mike6960]

No, I am not very clear I am afraid. Sorry for that.
In my example both the question and the answer are different events. Main difference is that the question does not contain the field 'PreviousRequestId' . I want to check that for every question there is a answer received

[niketn]

@Mike6960, can you add some sample value so that your query is more clear?

[Mike6960]

Initial request (question)

{ [-]
date: 2017-11-21T10:41:10,599
level: INFO
logger: XYX
message: { [-]
BusinessId: 1360158
BusinessObject: PP
CreationDate: 2017-11-21T10:41:10.599+01:00
MessageId: 1
Origin: test
RequestId: 01a7bf89-8fff-473e-a713-5f006014b087
component: CKF
location: FFBCD
}
thread: [dom_mst].domeinHttpsListener.worker.168
}

Answer:
{ [-]
date: 2017-11-21T10:41:10,875
level: INFO
logger: XYX
message: { [-]
BusinessId: 1360158
BusinessObject: PP
CreationDate: 2017-11-21T10:41:10.599+01:00
MessageId: 1
Origin: test
PreviousRequestId: 01a7bf89-8fff-473e-a713-5f006014b087
RequestId: 428aa3b9-11d7-4692-841b-388cf1914ef0
component: CF
location:FFBCA
}
thread: [dom_mst].domeinHttpsListener.worker.168
}

[niketn]

@Mike6960, with the data provided, what is the output you want?

The two events i.e. question and answer have same MessageId is this intentional? would they be unique for each set of question and answer?

[Mike6960]

Hai Niketnilay, i noticed that also last night. I requested the developer to ensure this Id is always unique for the set. Thank you (again) for the help

[niketn]

Great news, then correlation become fairly simple and fast using stats, the way @DalJeanis has mentioned below, however based on MessageId.

[Mike6960]

Still struggling, i need to group by message.BusinessId.
I have got:

| stats values(message.MessageId) as MessageId values(message.location) as location count by message.BusinessId |table *

MessageId count location message.BusinessId

143d7685-120e 5 Fetcha 4445646545465

2c659a59-6317 Fetchb

5c3d7f96-ed84 Fetchc

66ccf168-aa82

823d50a8-9997-

What I want is something like this:

MessageId count location message.BusinessId CreationDate

143d7685-120e 5 Fetcha 4445646545465 Date1

2c659a59-6317 Fetcha Date2

5c3d7f96-ed84 Fetchb Date3

66ccf168-aa82 Fetchb Date4

823d50a8-9997- Fetchc Date5

How can I create this? I've tried also withe the list command but then I run into maximum event

[niketn]

How about this, just add valued(CreationDate) as CreationDate to your stats command?

<YourBaseSearch>
| stats values(message.MessageId) as MessageId values(message.location) as location count values(CreationDate) as CreationDate as   by message.BusinessId 
| table *

[Mike6960]

Unfortunately this is also not giving the wanted results. It seems splunk gives only the unique values,