Event Correlation

[ninadmnaik]

I have two events:

Event 1:
transactionId=123 field_x=x_value

Event 2
transactionId=123 status=success

How can I correlate these two?
I want to create a timechart for “field_x” when “status=success”

So, basically, the search quey is:
transactionId field_x | timechart count by field_x

But I want to get all “field_x” only when status=success.

So, I guess this is equivalent to SQL IN() construct:
SELECT field_x from table where transactionId IN (SELECT transactionId from table where status=success);

I am trying to do a subsearch like:
source="source1" field_x=* transactionId [search source="source1" AND status=success | fields transactionId] | timechart count by field_x

Doesn't seem to be working.

[lguinn2 · Accepted solution]

Try this:

yoursearchhere | 
transaction transactionId | 
search status=success | 
timechart count by field_x

I think you were making it too hard! 🙂

[ninadmnaik]

Oh yeah, you're right. That did it. Thanks a bunch !!

[lguinn2]

The transaction consists of a set of events, all with the same transactionId. The search command applies to the entire transaction, not the individual events. So the AND should be okay.

[lguinn2]

What do you get when you just do

source="source1" field_x=* OR status=success |
transaction transactionId

[ninadmnaik]

As per your new suggestion, that won't work, because:
search status=success AND field_x="*"

For the above to work, both the fields should be in the same logging event right? But they aren't.
I have two different logging events as:

Event 1:
transactionId=123 field_x=x_value

Event 2
transactionId=123 status=success

[ninadmnaik]

No, I take it back. When I said it was working, I missed the following line (bold) in the query:

source="source1" field_x=* OR status=success |
transaction transactionId |
search status=success |
timechart count by field_x

If I add this line "search status=success", I don't get any results. And without checking whether "status=success" I will get all "field_x" values for which "status=failed" as well.

[lguinn2]

I think that you have some transactions that do not have field_x in them. Try this

source="source1" field_x=* OR status=success |
transaction transactionId |
search status=success AND field_x="*" |
timechart count by field_x

[ninadmnaik]

No, you are right. Updated my query to:

source="source1" field_x=* OR status=success |
transaction transactionId |
search status=success |
timechart count by field_x

Now it's giving me the chart.

However, one little thing. Along with the four values of "field_x" it's also showing a value "NULL". Wonder why is that.

Will update this when I find out about NULL.

Please let me know if you have an idea.

Thanks a bunch for your answer.

[ninadmnaik]

Oh yes, that does make sense. But this isn't working either. Splunk isn't finding any matching events.

Here's the updated query as per your suggestion.

source="source1" field_x=* |
transaction transactionId |
search status=success |
timechart count by field_x