Splunk Search

combine two search in a one table

indeed_2000
Motivator


Hi
I have log file like this, need to extract "id" from lines that A=20 and match these lines to lines where that B=10, finally show them in a single table.


1-where A=20 export "id(s)" from these lines:
07:59:42.213 app module: Z[200]id[12]A[20]
07:59:42.213 app module: Y[300]id[88]A[20]

2-if "id" that export in pervious step and B=10 matched join them and make table. share field is "id" between these lines.
07:58:21.533 app module: Q[230]id[12]B[10]
07:58:21.533 app module: V[230]id[88]B[10]

expected result:
id        A      B
12     20  10
88     20  10

any idea?

Thanks,

Labels (5)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

As I said - works for me

Screenshot_20211009-213455_Firefox Beta.jpg

View solution in original post

PickleRick
SplunkTrust
SplunkTrust
<your search> | stat values(A) as A values(B) as B by id

Unless you want something else. The connection between steps 1 and 2 is not clear.

0 Karma

indeed_2000
Motivator

@PickleRick which part not clear? share field is "id" between step 1 and step 2.

1-where A=20, export "id(s)" from these lines:
07:59:42.213 app module: Z[200]id[12]A[20]
07:59:42.213 app module: Y[300]id[88]A[20]

2-in below lines, if "id" that export in pervious step and B=10 were matched join them and make table.
07:58:21.533 app module: Q[230]id[12]B[10]
07:58:21.533 app module: V[230]id[88]B[10]

expected result:
id        A      B
12     20  10
88     20  10

0 Karma

PickleRick
SplunkTrust
SplunkTrust

What is not clear is that you're writing about matching on id value but additionaly give some conditions for A and B. I presume that you wanted to just point to those values and didn't mean this as additional conditions.

0 Karma

indeed_2000
Motivator

@PickleRick main problem is A & B not in same line, thats why i need to first find A=20 then with id find lines that B=10.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

That's why you do values() from different fields by id. Have you tried that search I posted?

 

 

0 Karma

indeed_2000
Motivator

Yes, I try that but not return appropriate result, return lots of 0 for A and B while they have value.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Zeros? Didn't you do count(A/B) instead of values()?

0 Karma

indeed_2000
Motivator

Nope, i didn’t divide them.

would you please eval these four lines of log in your splunk then run your spl command?

07:59:42.213 app module: Z[200]id[12]A[20]
07:59:42.213 app module: Y[300]id[88]A[20]

07:58:21.533 app module: Q[230]id[12]B[10]
07:58:21.533 app module: V[230]id[88]B[10]

Thanks 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Wait, wait, wait. What do you mean "eval these lines"? I can of course define fields with proper values and I know what values I'll get. But it surely won't work if you don't have proper extractions. You have proper extractions, don't you?

Anyway, here you go - I define some field values then do the stats

PickleRick_0-1633805856530.png

 

0 Karma

indeed_2000
Motivator

I mean use

makeresults 
| eval _raw=

There is no field extraction issue here, all fields extract correctly.

you can extract easily with this 

| rex  "id\[(?<id>\d+)"
| rex  "A\[(?<A>\d+)"

| rex  "B\[(?<B>\d+)"  | table id A B

0 Karma

PickleRick
SplunkTrust
SplunkTrust

As I said - works for me

Screenshot_20211009-213455_Firefox Beta.jpg

indeed_2000
Motivator

After several hours workaround, find out what is the reason. 
“id”(s) wasn’t unique in log file, thats why I get several 0 and multiple value for each id!

 Thanks 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Well, if the id's were not unique, either way you did it (with stats or with join) it wouldn't work properly 😉

0 Karma

indeed_2000
Motivator

Thank you for answer,

where is the "join", "appendcols", "where"?

0 Karma

PickleRick
SplunkTrust
SplunkTrust

You don't need to use them... if you don't need them 🙂

You're thinking too much like with a declarative programming language or SQL. But SPL works differently. And if you can avoid using complicated operations, it's best to do so.

So you just need to do your search to list the events which have your A, id and B fields and run them through this stat I showed you.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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