Splunk Search

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

khubyarb
Path Finder

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

khubyarb
Path Finder

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.

0 Karma

woodcock
Esteemed Legend

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...