topic Re: Search query to remove results from another source. in Splunk Search

Search query to remove results from another source.

rodrigomarfei — Wed, 25 Aug 2021 14:23:03 GMT

I have the following sourcers: "inserted" and "deleted"

In the "inserted" i have these fields:
Id, Timestamp
1, 2021-08-18T19:39:31.3003273
2, 2021-08-18T02:25:05.786293
3, 2021-08-18T19:39:31.301158
etc....

In the "deleted" I have the same fields:
Id, Timestamp
1, 2021-08-18T19:39:31.3003234
1, 2021-08-18T19:28:00.8425431
1, 2021-08-18T19:27:07.2603396
2, 2021-08-18T18:57:52.3556542
2, 2021-08-18T15:06:19.3365628
3, 2021-08-18T15:06:02.5264226
3, 2021-08-18T12:06:29.5371453
3, 2021-08-18T11:55:40.7562728
3, 2021-08-18T03:22:06.3672773

I need to filter the events in the 'inserted' that are newer than in the 'deleted', where the Id's are the same in both sourcers and the timestamp in the 'inserted' is greater than the Timestamp in the 'deleted'.

I've managed to set up a search for one ID and manually setting the last timestamp that I found in the 'deleted', as per below:

index=something source=inserted Id=1 | eval data_inserted = strptime('Timestamp', "%Y-%m-%dT%H:%M:%S.%Q") | eval data_deleted = "2021-08-18T19:39:31.3003234" | eval data_deleted = strptime('data_deleted', "%Y-%m-%dT%H:%M:%S.%Q") | where data_inserted > data_deleted

My goal and help needed is on how to do this automatically for the IDs and Timestamp I have in the source='deleted'.

Your help is very much appreciated.

Thank you!

Re: Search query to remove results from another source.

scelikok — Wed, 25 Aug 2021 15:01:34 GMT

Hi @rodrigomarfei,

Please try below search;

index=something (source=inserted OR source=deleted) | eval timestamp = strptime('Timestamp', "%Y-%m-%dT%H:%M:%S.%Q") | sort -timestamp | dedup id | search source=inserted

Re: Search query to remove results from another source.

rodrigomarfei — Wed, 25 Aug 2021 15:52:13 GMT

@scelikok

Thanks, but, it's only returning one event from one ID.

I need to get the last event for every ID that is in the 'inserted'.

I appreciate your help.

Re: Search query to remove results from another source.

scelikok — Wed, 25 Aug 2021 16:15:42 GMT

Hi @rodrigomarfei,

Using your sample data below search is working. There is only a typo on Id field but I think you already corrected.

| makeresults | eval _raw="Id,Timestamp,source 1,2021-08-18T19:39:31.3003234,deleted 1,2021-08-18T19:28:00.8425431,deleted 1,2021-08-18T19:27:07.2603396,deleted 2,2021-08-18T18:57:52.3556542,deleted 2,2021-08-18T15:06:19.3365628,deleted 3,2021-08-18T15:06:02.5264226,deleted 3,2021-08-18T12:06:29.5371453,deleted 3,2021-08-18T11:55:40.7562728,deleted 3,2021-08-18T03:22:06.3672773,deleted 1,2021-08-18T19:39:31.3003273,inserted 2,2021-08-18T02:25:05.786293,inserted 3,2021-08-18T19:39:31.301158,inserted" | multikv forceheader=1 | fields - _raw _time linecount | eval timestamp = strptime('Timestamp', "%Y-%m-%dT%H:%M:%S.%7Q") | sort -timestamp | dedup Id | search source=inserted

Is it possible that your real data is somehow different?

Re: Search query to remove results from another source.

rodrigomarfei — Fri, 27 Aug 2021 14:06:53 GMT

Thank you,

The problem was that sort limits to 10.000 results and I have more than 6 millions events.

All I had to do was to set sort to unlimited. 🙂