topic Re: How to compare events from two sources to find outliers in data? in Splunk Search

How to compare events from two sources to find outliers in data?

bcusick — Tue, 19 Aug 2014 15:52:45 GMT

Hi,

I'm trying to compare events from two sources to show where the outliers are (they "should" be the same but we know that there are discrepancies.

I can compare "number of rows/events" easily with a "chart count by source" command, but I also want to check the integrity of the field values.

Basically, each event has 10 fields (same ten fields in each source). How do I check that they are the same, and return some kind of message/raw event/field value if they are not the same?

Thanks.

Re: How to compare events from two sources to find outliers in data?

somesoni2 — Tue, 19 Aug 2014 15:59:34 GMT

Does both sources have timestamp and do they differ? What should be the order of rows/events for field comparison;first row of source1 with first row of source2?? Are field names static?

Re: How to compare events from two sources to find outliers in data?

bcusick — Tue, 19 Aug 2014 16:19:58 GMT

This is for reporting to regulators, so everything should be EXACTLY the same. Same timestamp, same field order, etc. I want to be able to check if any fieldname is different (I can pivot on field TRANSACTION_ID) for everything else. All field names static, and yes, If row numbers are the same (which they should be) I should be able to compare row1.source1 to row1.source2

Re: How to compare events from two sources to find outliers in data?

somesoni2 — Tue, 19 Aug 2014 18:40:15 GMT

May be something like this

source=source1 | eval source1Data=Field1."##".Field2."##"...<<all 10 fields concatenated>>."##".Field10 | appendcols [search source=source2 | eval source2Data=Field1."##".Field2."##"...<<all 10 fields concatenated>>."##".Field10]
| eval result=if(source1Data=source2Data,"Matched","Unmatched")

Re: How to compare events from two sources to find outliers in data?

bcusick — Tue, 19 Aug 2014 21:13:37 GMT

This looks like it will work. Will provide an update tomorrow. Will this know to compare source1 event1 with source2 event1?

Re: How to compare events from two sources to find outliers in data?

somesoni2 — Tue, 19 Aug 2014 21:57:15 GMT

Since I used appendcols, it will compare source1 event1 with source2 event1. It would fail for the cases no of rows differ in the sources.

Re: How to compare events from two sources to find outliers in data?

bcusick — Wed, 20 Aug 2014 19:30:07 GMT

This keeps telling me I have mismatched "]" but I checked multiple times to ensure it's correct. Could the fact that my fields contain "." and "-" have anything to do with this?

Re: How to compare events from two sources to find outliers in data?

somesoni2 — Wed, 20 Aug 2014 20:35:04 GMT

Can you post your query? and may be some sample data?

Re: How to compare events from two sources to find outliers in data?

bcusick — Mon, 28 Sep 2020 17:24:40 GMT

Would it be possible to do this with raw data? Meaning using the field "_raw"? This runs, but results are incorrect due to the sources being different..Here's that example:

source="D:\Bluesheets\ExtractFromFES.csv" eval FESdata=_raw | appendcols [search source="D:\Bluesheets\SentToReg.csv" | eval Regdata=_raw] | eval result=if(FESdata=Regdata,"Matched","Unmatched") | table result

Re: How to compare events from two sources to find outliers in data?

SierraX — Mon, 20 Jul 2020 04:41:02 GMT

Ok it's maybe a bit late … but for future searchers the other answers are to complicated, no help or wrong
```
index=main sourcetype="test2" | stats values(source) as sources by _raw | eval sources=if(mvcount(sources)>1,"match","no match")
```
Beware of linecounts>1 in the main search, this could create false "no match"