Subsearch filter

maartendhondt — Wed, 15 May 2019 07:47:38 GMT

In an index for a specific host I have log lines like this:

2019-05-15T06:09:56+00:00|6eb44e3c-d93e-4a43-b3f0-560a03459233|some logging

This is the timestamp, an id and the actual log line.

And for another host in the same index i have log lines like this:

2019-05-15T06:09:56,241+0000|6eb44e3c-d93e-4a43-b3f0-560a03459233|Request blocked because of blacklisted user.

This is a timestamp, an id and the actual log line.

I have a query for the first host that counts the number of 5xx response codes:

index="myIndex" host="firstHost" responseCode >199 "some search field" | rangemap field=responseCode 2xx=200-299 3xx=300-399 4xx=400-499 5xx=500-599 | rename range AS "Http Status" | search "Http Status"=5xx | append [stats count | eval _time=-1 | where count=0 | fields - count] | timechart span=15m count by "Http Status"

My problem is that this query is counting more occurrences than I'd like. I would like to restrict the counted lines to lines that do not have an activity id for which the activity id also appears on the second host on a log line that contains "Request blocked because of blacklisted user".
So:

Search first host and extract activityId of each log line and return list of activity ids: rex field=_raw "\|(?<activityId>.*?(?=\|)\|)" | table activityId
Remove an activity id out of the list if it occurs on the second host in the same line as "Request blocked because of blacklisted user"
Execute my above query but only for log lines that contain an activity id from those that are still in the list.

I assume this'll require a subsearch because it uses search results from one host to filter results on another host. But I do not know where to start to create a query like this.

Re: Subsearch filter

whrg — Wed, 15 May 2019 12:45:25 GMT

You should create a field extraction for the activityId field, so that it gets extracted automatically for all events. This way, you don't need the rex command in your search.

You are right: You should use a subsearch for this:

index="myIndex" host="firstHost" NOT [search index="myIndex" host="firstHost" "Request blocked because of blacklisted user" | table activityId] ...

Alternatively, with the rex command (I improved the regex a little):

index="myIndex" host="firstHost" | rex field=_raw "\|(?<activityId>[^\|]+)\|" | search NOT [search index="myIndex" host="firstHost" "Request blocked because of blacklisted user" | rex field=_raw "\|(?<activityId>[^\|]+)\|" | table activityId] ...

This will filter all events in the base search which have a matching activityId in the subsearch.

topic Re: Subsearch filter in Splunk Search

Subsearch filter

Re: Subsearch filter