Splunk Search

Looking for matching events with "where" and calculating them

MrFaria25
Observer

I'm creating a query where I want to get an id from a log in one side (first search) and
in the second search I just want to bring the results that have the ids of the first search.
Then I want to calculate the difference between them.
Something like:

 

index=anything source=anything route1 Payload OK | rex field=_raw "\:[0-9]{4} \- (?<IDROUTE1>[0-9a-f{8}) \-" | stats count(_raw) as CROUTE1 | table IDROUTE1 | appendcols [search index=anything source=anything route2 Payload OK
| rex field=_raw "\:[0-9]{4} \- (<IDROUTE2>[0-9a-f{8})
\-" | stats count(_raw) as CROUTE2 | table IDROUTE2]
| where IDROUTE1=IDROUTE2 | eval TOTAL=CROUTE1-CROUTE2 | table TOTAL

 

 

what is not working is to count the events using where I guess.
Searches when done separately bring me the correct results.
In Events show me a correct number of events but in Statistics show me 0.

 

Labels (2)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

There is something strange going on.

Let's analyze it from the top.

index=anything source=anything route1 Payload OK
| rex field=_raw "\:[0-9]{4} \- (?<IDROUTE1>[0-9a-f{8}) \-"
| stats count(_raw) as CROUTE1
| table IDROUTE1

 You're searching for all events containing given terms.

Then you extract a field by means of the rex command. So far so good.

Then you do stats (you can just do stats count. You don't have to count(_raw). But that's a minor remark.

And then magic happens.

You got single value from your stats command but then you want to do table (which you shouldn't use in the middle of your search but that's another story) showing only a field called IDROUTE1. But there are no fields called that in the output of your preceeding stats command. Stats will give you just one row of results with just one value in a field called CROUTE1.

So after this part you'll get no results at all (because even the single value you got from stats got cut away by table). And since you get no results at all any subsequent appendcols will not include anything from the original search.

And the search you're trying to appendcol has also the exact same flaw, so you're effectively trying to append columns from one empty search to another one.

That's one thing.

Another thing is that appendcols would give you two searches "glued" together but just in the order of original results so there wouldn't have to be any relation between them. So that's most probably not what you had in mind.

0 Karma

MrFaria25
Observer

Thank you for spending your time answering me. I appreciate.
When I do this:
index=anything source=anything route1 Payload OK
| rex field=_raw "\:[0-9]{4} \- (?<IDROUTE1>[0-9a-f{8}) \-" | table IDROUTE1

Splunk show me the id of each log. But I can take off that part of my search.
I was only using it to debug the query because it showed me the id of each log.
What matters to me is the calculation that is made.

Yeah,  I need there to be a relationship between the two searches. Something like that:
4545-3a4a5bec route1 Payload OK

4545-3a4a5bec route2 Payload OK

That 2 logs above I want to count(route1 and route2 increment) because the id of route1 exists in id of route2 but when:

4545-3a4a5bec route1 Payload OK

2535-6a3f5bab route2 Payload OK

The id of route1 dont exists in route2 I want only to count route1 and not increment route2

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Your description is a bit vague but typically you use a similar approach to what @gcusello already showed - you search for all your events, you extract a field clasifying your event into - in your case - one of the routes. Then you use clever statsing to group the events by ID or whatever.

0 Karma

MrFaria25
Observer

It's simple. What I wanna do is the difference operation between two sets: to get every item that is in A but not in B. And I use the id to differentiate the logs:

First set

1 - 4545-3a4a5bec route1 Payload OK

Second set

1 - 4545-3a4a5bec route2 Payload OK

In the situation above the id exists in two sides then the results of difference operation is 0.

But in the situation below:

First set

4545-3a4a5bec route1 Payload OK

Second set

2535-6a3f5bab route2 Payload OK

the id of the first set does not exists in the second set then the results of difference operation is 1.

 

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

It's simple to you, not us. Remember that we don't know your events, don't know how many are there and so on.

You're talking about "sets" but keep up bringing single rows of results.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @MrFaria25,

appendcols isn't the command for you, please see this different approach:

I suppose that rout1 and route2 are two different strings, if they are fields the ondition is a little different:

index=anything source=anything Payload OK (route1 OR route1)
| rex field=_raw "\:[0-9]{4} \- (?<IDROUTE>[0-9a-f{8}) \-" 
| stats 
   count(eval(searchmatch("route1"))) AS CROUTE1
   count(eval(searchmatch("route2"))) AS CROUTE2
| eval TOTAL=CROUTE1-CROUTE2 
| where TOTAL=0
| table TOTAL

Ciao.

Giuseppe

 

0 Karma

MrFaria25
Observer

Thanks a lot, Giuseppe, for your answer.

In the moment I can't to test your query but I think it's right. 

One doubt:

index=anything source=anything Payload OK (route1 OR route1)
| rex field=_raw "\:[0-9]{4} \- (?<IDROUTE>[0-9a-f{8}) \-"

 

It's give me only route1 Payload OK and route2 Payload OK(that are 2 strings from diferrent logs) when o id are equal? Ex:

4545-3a4a5bec route1 Payload OK

4545-3a4a5bec route2 Payload OK

That 2 logs above I want to count(route1 and route2 increment) because the id of route1 exists in id of route2 but when:

4545-3a4a5bec route1 Payload OK

2535-6a3f5bab route2 Payload OK

The id of route1 dont exists in route2 I want only to count route1 and not increment route2.

Thanks again!

 

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