Splunk Search

how to find equal values

Mike6960
Path Finder

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'

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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
Path Finder

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
SplunkTrust
SplunkTrust

Great! Glad you got it sorted out.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma

Mike6960
Path Finder

No, that does not work

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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.

0 Karma

Mike6960
Path Finder

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'

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma

Mike6960
Path Finder

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.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma

Mike6960
Path Finder

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Mike6960
Path Finder

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
}

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Mike6960
Path Finder

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Mike6960
Path Finder

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

0 Karma

niketn
Legend

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 *
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Mike6960
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...