Search query to remove results from another source.

[rodrigomarfei]

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!

[scelikok]

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?

[rodrigomarfei]

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

[scelikok · Accepted solution]

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

[rodrigomarfei]

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