topic Re: how to find equal values in Splunk Search

how to find equal values

Mike6960 — Wed, 22 Nov 2017 14:48:51 GMT

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'

Re: how to find equal values

niketn — Wed, 22 Nov 2017 14:53:55 GMT

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

Re: how to find equal values

skoelpin — Wed, 22 Nov 2017 15:01:49 GMT

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

Re: how to find equal values

Mike6960 — Wed, 22 Nov 2017 15:02:21 GMT

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
}

Re: how to find equal values

Mike6960 — Wed, 22 Nov 2017 15:08:40 GMT

No, that does not work

Re: how to find equal values

skoelpin — Wed, 22 Nov 2017 15:10:48 GMT

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.

Re: how to find equal values

Mike6960 — Wed, 22 Nov 2017 15:15:08 GMT

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'

Re: how to find equal values

skoelpin — Wed, 22 Nov 2017 15:34:11 GMT

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

Re: how to find equal values

Mike6960 — Wed, 22 Nov 2017 15:45:09 GMT

The events can be like this (question and answer)

Initial request (question)

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

Re: how to find equal values

skoelpin — Wed, 22 Nov 2017 15:58:14 GMT

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

Re: how to find equal values

Mike6960 — Wed, 22 Nov 2017 16:04:23 GMT

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

Re: how to find equal values

niketn — Wed, 22 Nov 2017 16:59:50 GMT

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

Re: how to find equal values

DalJeanis — Wed, 22 Nov 2017 17:26:37 GMT

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

Re: how to find equal values

Mike6960 — Thu, 23 Nov 2017 09:31:42 GMT

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

Re: how to find equal values

Mike6960 — Thu, 23 Nov 2017 09:33:41 GMT

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.

Re: how to find equal values

niketn — Thu, 23 Nov 2017 09:39:36 GMT

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

Re: how to find equal values

Mike6960 — Thu, 23 Nov 2017 16:59:32 GMT

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

Re: how to find equal values

niketn — Thu, 23 Nov 2017 18:49:15 GMT

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 *

Re: how to find equal values

DalJeanis — Sat, 25 Nov 2017 02:05:01 GMT

Great! Glad you got it sorted out.

Re: how to find equal values

Mike6960 — Mon, 27 Nov 2017 09:37:24 GMT

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