topic Re: Left join - find missing data from second index in Splunk Search

Left join - find missing data from second index

mattiasrs — Fri, 05 Feb 2021 11:38:02 GMT

Hello, I am quite new to Splunk and this is my first post. Hoping that I can get some help from this awesome community.

I have two systems, System A and System B. System A receives customer information which is then sent to System B . The data in both systems have the exact same fields and a unique Customer ID with the same name in both systems.

I want to create a dashboard where I can select a time period and see only problematic customers that only exist in System A, meaning they haven't been sent to System B for some reason.

This is my search to see all the data:

index=systemA OR index=systemB

| fields customer_ID, systemA_Timestamp, systemB_Timestamp

| stats values(*) as * by customer_ID

| table customer_ID, systemA_Timestamp, systemB_Timestamp

So to summarize, I want to see customer_IDs that only exist in System A. I am not sure which function to use here. I have been experimenting with isnull(systemB_Timestamp) with no success. Join is not an option as the limit of 50 000 might be a problem.

Would be very grateful for any help!

Re: Left join - find missing data from second index

saravanan90 — Fri, 05 Feb 2021 17:06:11 GMT

Please check if the below query works.

index=systemA NOT [search index=systemB | stats count by customer_ID | table customer_ID]

Re: Left join - find missing data from second index

mattiasrs — Mon, 08 Feb 2021 09:14:50 GMT

Hello,

No this returns only the raw events, not in a table format.

I tried moving |table outside the [ ] but this returns data that exist in both indexes, just not showing the data from index B.

Re: Left join - find missing data from second index

ITWhisperer — Mon, 08 Feb 2021 09:28:56 GMT

Try something like this

index=systemA OR index=systemB | fields customer_ID, systemA_Timestamp, systemB_Timestamp | stats values(*) as * by customer_ID | fillnull value="NotPresent" systemB_Timestamp | where systemB_Timestamp="NotPresent" | table customer_ID, systemA_Timestamp, systemB_Timestamp

Re: Left join - find missing data from second index

saravanan90 — Mon, 08 Feb 2021 11:36:09 GMT

Hello,

Is the table command outside gives still raw events of both the index.

index=systemA NOT [search index=systemB | stats count by customer_ID | table customer_ID] | table customer_ID, systemA_Timestamp

Re: Left join - find missing data from second index

mattiasrs — Mon, 08 Feb 2021 11:38:34 GMT

Thank you!

With this I am able to achieve what I want.

Do you know if this is best practice?

Re: Left join - find missing data from second index

ITWhisperer — Mon, 08 Feb 2021 11:45:54 GMT

I guess that might depend on what your actual data looks like and what your criteria for determining "best" is. Using stats is usually better than join, fillnull should be reasonably inexpensive as it can be done in-stream / in parallel, same goes for the where clause.

Re: Left join - find missing data from second index

mattiasrs — Mon, 08 Feb 2021 11:47:49 GMT

Many thanks!