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!

Index This | I’m short for "configuration file.” What am I?

May 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with a Special ...

New Articles from Academic Learning Partners, Help Expand Lantern’s Use Case Library, ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Your Guide to SPL2 at .conf24!

So, you’re headed to .conf24? You’re in for a good time. Las Vegas weather is just *chef’s kiss* beautiful in ...