topic Re: Is there a more efficient solution than my current search to validate whether data from two different sources is the same? in Splunk Search

Is there a more efficient solution than my current search to validate whether data from two different sources is the same?

khubyarb — Wed, 29 Jun 2016 19:08:39 GMT

I am trying to validate whether data from two separate sources is the same. I have indexed two csv files of 450,000+ records in two separate indexes and am trying to compare now. Currently am joining on a unique field in both tables and matching on certain field values available in the data. Having a problem that my data set is too large for the subsearch (limit is 50,000) to handle and I cannot access limits.conf to increase the subsearch limit. Can someone help me with an alternative and hopefully more efficient solution? My goal is to show the records that are different and the values within that differ between the two tables This is the type of search I am currently using shortened for the use of this question:

index="source1" sourcetype=type1  | 

eval unique = uniqueField | 

stats count by ID, unique, field1, field2, field3, field4 |

sort 0 ID, field2| 

eval allFields1 = field1.field2.field3.field4|

eval a = field1 |eval b = field2| eval c = field3 | eval d = field4 |

join unique   [search index="source2" sourcetype=type2 | 

eval unique= uniqueField |

stats count by ID, unique, field1, field2, field3, field4 |

sort 0 ID, field2 |

eval allFields2 = field1.field2.field3.field4 |

eval a2 = field1 |eval b2 = field2| eval c2 = field3 | eval d2 = field4  ]|

eval fieldA=if(a!=a2,"Table 1: '"+a+"' | Table 2: '"+a2+"'", "No differences") |
eval fieldB=if(b!=b2,"Table 1: '"+b+"' | Table 2: '"+b2+"'", "No differences") |
eval fieldC=if(c!=c2,"Table 1: '"+cr+"' | Table 2: '"+c2+"'", "No differences") |

where fields1!=fields2 |
table unique ID fieldA fieldB fieldC

Hopefully that's clear. Let me know if you need more information.

Re: Is there a more efficient solution than my current search to validate whether data from two different sources is the same?

woodcock — Wed, 29 Jun 2016 20:04:05 GMT

Try this:

(index="source1" sourcetype=type1) OR (index="source2" sourcetype=type2)
| eval unique = uniqueField
| stats count BY sourcetype ID unique field1 field2 field3 field4
| sort 0 ID, field2
| eval allFields = field1.field2.field3.field4
| eval a = field1 |eval b = field2 | eval c = field3 | eval d = field4
| stats dc(*) AS dc_* values(*) AS * BY unique

This creates distinct counts and multivalued fields so you can put in whatever comparison you need across the sourcetypes.

Re: Is there a more efficient solution than my current search to validate whether data from two different sources is the same?

khubyarb — Thu, 07 Jul 2016 21:42:06 GMT

Thanks for the response. Sorry but I am rather new to Splunk and not too sure how to use this to compare over fields between the indexes as you mentioned that I should be able to do.

Re: Is there a more efficient solution than my current search to validate whether data from two different sources is the same?

woodcock — Fri, 08 Jul 2016 01:51:28 GMT

Just run the search and peel back the layers to see how it works (or to fix something that might be wrong if it doesn't give you what you expect).