Splunk Search

Search query to remove results from another source.

rodrigomarfei
Explorer

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!

Labels (1)
0 Karma
1 Solution

scelikok
SplunkTrust
SplunkTrust

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
If this reply helps you an upvote and "Accept as Solution" is appreciated.

View solution in original post

scelikok
SplunkTrust
SplunkTrust

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

,

scelikok_0-1629908079548.png

Is it possible that your real data is somehow different?

 

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

rodrigomarfei
Explorer

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

 

0 Karma

scelikok
SplunkTrust
SplunkTrust

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
If this reply helps you an upvote and "Accept as Solution" is appreciated.

rodrigomarfei
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...