whats wrong with this query??

[super_edition]

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

[gcusello]

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

[PickleRick]

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.

[super_edition]

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

[gcusello]

Hi @super_edition ,

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

Ciao.

Giuseppe

[super_edition]

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.

[gcusello]

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

[super_edition]

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	3	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	3	1234234 343242 3423424	order_1010 order_2020 order_3030

[gcusello · Accepted solution]

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

[super_edition]

@gcusello 

I was able to get the desired output with inner join

[gcusello]

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

[PickleRick]

+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.