topic Re: Compare 2 datasets in Splunk Search

Compare 2 datasets

ppanchal — Mon, 28 Jun 2021 21:25:27 GMT

I have 2 data sets

index=support source=sites earliest=-1d@d latest=-0d@d
index=support source=sites earliest=-0d@d latest=now

I want to pull out that data which is changed in data set 2 as compared to data set 1

Re: Compare 2 datasets

bowesmana — Mon, 28 Jun 2021 21:36:50 GMT

You will need to provide a little more information on the fields in your data you are looking to compare and what output you will expect to see at the end.

Can you provide an example?

Re: Compare 2 datasets

ppanchal — Mon, 28 Jun 2021 21:48:30 GMT

We have a field called Site_id which is a string.

Example:

Set 1 - Site id = a1, a2, a3

Set 2 - Site id = a2, a3, a4

My result should be Site id = a1, a4

Re: Compare 2 datasets

bowesmana — Mon, 28 Jun 2021 22:00:36 GMT

Try this

(index=support source=sites earliest=-1d@d latest=-0d@d) OR (index=support source=sites earliest=-0d@d latest=now) | bin _time span=1d@d | stats count by _time Site_id | stats values(_time) as _time by Site_id | where mvcount(_time) = 1

This groups the sites by date then counts the dates that site has been found and only looks for results where there is only one time value.

Hope this helps

Re: Compare 2 datasets

ppanchal — Tue, 29 Jun 2021 20:13:16 GMT

This did not work, I tried below,

(index=support source=sites SITE_ID=S028 AND SITE_ID=S056 earliest=-1d@d latest=-0d@d) OR (index=support source=sites SITE_ID=S028 AND SITE_ID=S056 AND SITE_ID=S10 earliest=-0d@d latest=now)
| bin _time span=1d@d | stats count by _time SITE_ID | stats values(_time) as _time by SITE_ID | where mvcount(_time) = 1

It gives me 0 events/results. Can you please help?

Re: Compare 2 datasets

bowesmana — Wed, 30 Jun 2021 02:00:28 GMT

Can you take out the 'where' clause, so you can see what results you get back. If you replace the where clause with

| eval date=strftime(_time, "%F")

then you will get a date column, where it shows you the dates of data it has seen for those sites.

Re: Compare 2 datasets

ppanchal — Tue, 06 Jul 2021 16:43:39 GMT

No this will not help me. I have like 500-600 site IDs. I will have to go through the entire list to see the IDs are present for both the dates.

Any other solution?

I only want to find the sites ids that has only 1 occurrence.

Re: Compare 2 datasets

sanjeev543 — Tue, 06 Jul 2021 17:45:30 GMT

@ppanchal See if the below logic helps!!

Re: Compare 2 datasets

bowesmana — Sun, 11 Jul 2021 23:10:53 GMT

It shouldn't matter how many you have - the point is to see what your search is returning so the first line would be enough to investigate - it might be that your time constraints in the query are not working in your environment in that 'now' is not giving you any window between -0d@d

you could change the time constraints to

(index=support source=sites earliest=-2d@d latest=-1d@d) OR (index=support source=sites earliest=-1d@d latest=@d)

which will compare two days ago to yesterday