Here is a very simple example of "joining" two different datasets together based on their common ID. Almost all of the example is just setting up some example data. What you really need are the last ...
See more...
Here is a very simple example of "joining" two different datasets together based on their common ID. Almost all of the example is just setting up some example data. What you really need are the last 3 lines. If you paste this to a search window it will randomly return you some results if the PRODUCT contains MISMATCH - if you remove the last line of the example you will all results of the made up data. | makeresults
| fields - _time
``` Make some data for Sourcetype=autos ```
| eval sourcetype="autos"
| eval MAKE=split("Audi,Porsche,Mercedes",",")
| mvexpand MAKE
| eval MODEL=case(MAKE="Audi", split("AU-123,AU-988", ","), MAKE="Porsche", split("PO-123,PO-988", ","), MAKE="Mercedes", split("MX-123,MX-988", ","))
| mvexpand MODEL
| eval VIN=case(MAKE="Audi", split("AU-VIN:12345678,AU-VIN:9876543", ","), MAKE="Porsche", split("PO-VIN:12345678,PO-VIN:9876543", ","), MAKE="Mercedes", split("MX-VIN:12345678,MX-VIN:9876543", ","))
| mvexpand VIN
| eval VIN=MODEL.":".VIN
``` Make some identical data for Sourcetype=autos ```
| append [
| makeresults
| fields - _time
| eval sourcetype="cars"
| eval MANUFACTURER=split("Audi,Porsche,Mercedes",",")
| mvexpand MANUFACTURER
| eval PRODUCT=case(MANUFACTURER="Audi", split("AU-123,AU-988", ","), MANUFACTURER="Porsche", split("PO-123,PO-988", ","), MANUFACTURER="Mercedes", split("MX-123,MX-988", ","))
| mvexpand PRODUCT
| eval SN=case(MANUFACTURER="Audi", split("AU-VIN:12345678,AU-VIN:9876543", ","), MANUFACTURER="Porsche", split("PO-VIN:12345678,PO-VIN:9876543", ","), MANUFACTURER="Mercedes", split("MX-VIN:12345678,MX-VIN:9876543", ","))
| mvexpand SN
| eval SN=PRODUCT.":".SN
| eval PRODUCT=PRODUCT.if(random() % 100 < 10, "-MISMATCH", "")
]
``` Take the common field ```
| eval COMMON_ID=if(sourcetype="autos", VIN, SN)
| stats values(*) as * by COMMON_ID
| where MAKE!=MANUFACTURER OR MODEL!=PRODUCT Don't ever consider JOIN as the first option - it's not a Splunk way of doing things and has numerous limitations. Splunk uses stats ... BY COMMON_FIELD. Hope this helps