topic Re: whats wrong with this query?? in Splunk Search

whats wrong with this query??

super_edition — Wed, 06 Nov 2024 07:26:10 GMT

Hello Everyone,

I have below splunk query which will display the output as below

(index= index_1 OR index= index_2) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler" OR logger=PaymentStatusClientImpl") | search "* Did not observe any item or terminal signal within*" | spath "paymentStatusResponse.orderCode" | eval clusters=coalesce(openshift_cluster, kubernetes_cluster) | stats values(clusters) as cluster, values(host) as hostname, count(host) as count, values(correlation-id{}) as corr_id, values(paymentStatusResponse.orderCode) as order_code

From the above query, we have 2 loggers.
In the PaymentErrorHandler logger, I get the message containing: "Did not observe any item or terminal signal within"

In the EmsPaymentStatusClientImpl logger, I get the json response object containing "paymentStatusResponse.orderCode" value

In both loggers, we have correlation-id{} as common element.

I want to output a table containing cluster, hostname, count, corr_id and order_code

but the order code is alway empty.

Please help

Re: whats wrong with this query??

gcusello — Wed, 06 Nov 2024 07:41:49 GMT

Hi @super_edition ,

at first don't use the search command after the main search because your search will be slower:

(index= index_1 OR index= index_2) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler" OR logger=PaymentStatusClientImpl") "Did not observe any item or terminal signal within" | spath "paymentStatusResponse.orderCode" | eval clusters=coalesce(openshift_cluster, kubernetes_cluster) | stats values(clusters) as cluster, values(host) as hostname, count(host) as count, values(correlation-id{}) as corr_id, values(paymentStatusResponse.orderCode) as order_code

and the asterisk isn't mandatory in a string like your one.

Then review the use of spath command at https://docs.splunk.com/Documentation/Splunk/9.3.1/SearchReference/Spath :

(index= index_1 OR index= index_2) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler" OR logger=PaymentStatusClientImpl") "Did not observe any item or terminal signal within" | spath output=orderCode path=paymentStatusResponse.orderCode | eval clusters=coalesce(openshift_cluster, kubernetes_cluster) | stats values(clusters) as cluster values(host) as hostname count(host) as count values(correlation-id{}) as corr_id values(orderCode) as order_code

Ciao.

Giuseppe

Re: whats wrong with this query??

super_edition — Wed, 06 Nov 2024 08:57:11 GMT

Thanks @gcusello
I have amended the changes query but the output of order_code column is still empty.

order_code value "paymentStatusResponse.orderCode" comes from 1 of the 2 logger.

logger name PaymentStatusClientImpl

Re: whats wrong with this query??

gcusello — Wed, 06 Nov 2024 09:18:50 GMT

Hi @super_edition ,

running only your main search, do you see this field in interesting fields?

Ciao.

Giuseppe

Re: whats wrong with this query??

super_edition — Wed, 06 Nov 2024 10:05:30 GMT

Hello @gcusello

If I run the main search as below:

(index= index_1 OR index= index_2) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler" OR logger=PaymentStatusClientImpl")

I am able to see "paymentStatusResponse.orderCode" values in interesting field.

Re: whats wrong with this query??

gcusello — Wed, 06 Nov 2024 10:22:11 GMT

Hi @super_edition ,

this means that you have INDEXED_EXTRACTIONS=JSON in your props.conf and you don't need to use spath, please try this:

(index= index_1 OR index= index_2) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler" OR logger=PaymentStatusClientImpl") "Did not observe any item or terminal signal within" | eval clusters=coalesce(openshift_cluster, kubernetes_cluster) | stats values(clusters) AS cluster values(host) AS hostname count(host) AS count values(correlation-id{}) AS corr_id values(paymentStatusResponse.orderCode) AS order_code

only one thing: in the screenshot it isn't clear the field name, it seems that there's something before paymentStatusResponse.orderCode, can you check it? are you sure that the file name is exactly paymentStatusResponse.orderCode?

Ciao.

Giuseppe

Re: whats wrong with this query??

super_edition — Wed, 06 Nov 2024 12:30:24 GMT

Hello @gcusello

I have masked the field for the purpose of safety.

I tried by passing

values(paymentStatusResponse.orderCode) AS order_code

its not working.

With the below query

(index= index_1 OR index= index_1) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler") "Did not observe any item or terminal signal within" | eval clusters=coalesce(openshift_cluster, kubernetes_cluster) | stats values(clusters) as cluster values(host) as hostname count(host) as count values(message.tracers.ek-correlation-id{}) as corr_id

I am getting output as:

cluster

hostname

count

corr_id

hhj

yueyheh

1234234

343242

3423424

Now I want to add field paymentStatusResponse.orderCode, which comes from another logger "PaymentStatusClientImpl".

The common entity between these 2 loggers is message.tracers.ek-correlation-id{}. So that my final output will be

cluster

hostname

count

corr_id

order_code

hhj

yueyheh

1234234

343242

3423424

order_1010

order_2020

order_3030

Re: whats wrong with this query??

gcusello — Wed, 06 Nov 2024 13:29:04 GMT

Hi @super_edition ,

ok, in other words, you need to do a join with nother search, is it correct?

if you haven't so many events, you could use the join command.

If instead you're sure to have the message.tracers.ek-correlation-id{} field in all events, you could use this field as correlation key:

(index= index_1 OR index= index_1) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler") "Did not observe any item or terminal signal within" OR logger="PaymentStatusClientImpl" | eval clusters=coalesce(openshift_cluster, kubernetes_cluster) | stats values(clusters) as cluster values(host) as hostname count(host) as count values(paymentStatusResponse.orderCode) AS order_code BY message.tracers.ek-correlation-id{}

Ciao.

Giuseppe

Re: whats wrong with this query??

PickleRick — Wed, 06 Nov 2024 13:45:52 GMT

Luckily, at the beginning of the search Splunk is actually quite smart in optimizing out some common issues.

For example, if I run this

(index= index_1 OR index= index_2) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler" OR logger="PaymentStatusClientImpl")
| search "* Did not observe any item or terminal signal within*"

on my home Splunk instance (let's ignore the fact that I won't have any matching events obviously but that's not the point) and see the job detail dashboard I can see this

| search ("* Did not observe any item or terminal signal within*" (index=index_1 OR index=index_2) (kubernetes_namespace="kube_ns" OR openshift_namespace="ose_ns") (logger="PaymentErrorHandler" OR logger="PaymentStatusClientImpl"))

as optimized search. And if we go to job log we can see this

[ AND any did item not ns observe or signal terminal within* [ OR index::index_1 index::index_2 ] [ OR kube ose ] [ OR paymenterrorhandler paymentstatusclientimpl ] ]

As base lispy search.

As we can see, Splunk was not only able to "flat" both searches into single one but also noticed that the initial wildcard was before a major breaker and a such wouldn't affect the sought terms.

But as a general rule of thumb - yes it's a good practice to keep your searches "tidy" and avoid wildcards at the beginning of search terms.

Re: whats wrong with this query??

super_edition — Thu, 14 Nov 2024 07:37:25 GMT

@gcusello

I was able to get the desired output with inner join

Re: whats wrong with this query??

gcusello — Thu, 14 Nov 2024 08:03:46 GMT

Hi @super_edition ,

my hint is to continue to try using stats to reach the wanted output, because join is a very slow command that requires many resources and it's always better to avoid it.

Ciao.

Giuseppe

Re: whats wrong with this query??

PickleRick — Thu, 14 Nov 2024 09:58:04 GMT

+1 on that - don't use join unless there is absolutely no other way (or you have a very small dataset).

Not only it's relatively slow and resource-hungry, it has also pretty serious limitations and you can get wrong or incomplete results without knowing it.