Splunk Search

combine two search in a one table

indeed_2000
Builder


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
Champion

As I said - works for me

Screenshot_20211009-213455_Firefox Beta.jpg

View solution in original post

PickleRick
Champion
<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
Builder

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

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
Builder

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

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

 

 

0 Karma

indeed_2000
Builder

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

0 Karma

PickleRick
Champion

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

0 Karma

indeed_2000
Builder

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
Champion

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
Builder

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
Champion

As I said - works for me

Screenshot_20211009-213455_Firefox Beta.jpg

View solution in original post

indeed_2000
Builder

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
Champion

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
Builder

Thank you for answer,

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

0 Karma

PickleRick
Champion

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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!