topic Re: How to connect two different indexes by unique item code showing count / percent (with match) & (without match)? in Splunk Search

How to connect two different indexes by unique item code showing count / percent (with match) & (without match)?

timothytruax — Fri, 30 Mar 2018 16:10:38 GMT

I have 2 indexes.
1st index (Index1) has a unique item code (Item1) for an item when it enters a process.
2nd index (Index2) should have the same unique item code (Item2) when the item exits the process.
I want to produce report showing a count / percent where the (Index1.Item1 = Index2.Item2) & where (Index1.Item1 <> Index2.Item2).

Please show examples of SPL if possible and thank you!

Re: How to connect two different indexes by unique item code showing count / percent (with match) & (without match)?

deepashri_123 — Fri, 30 Mar 2018 16:36:31 GMT

Hey@timothytruax,

Try something like this,
if item1 and item2 has same fields then
index=index1 OR index=index2 | rename item2 as item1 | stats count dc(index) AS dc by item1 | where dc>1 | stats count .
This will give you the count of items having common values.

Let me know if this helps!!

Re: How to connect two different indexes by unique item code showing count / percent (with match) & (without match)?

timothytruax — Fri, 30 Mar 2018 17:58:53 GMT

Hey deepashri_123 -
What if there is possibility of duplicates in Index1 of the Item1 and possibility of duplicates in Index2 of Item2?
I want to be sure that Index1.Item1 is actually present in Index2.Item2.

Re: How to connect two different indexes by unique item code showing count / percent (with match) & (without match)?

deepashri_123 — Fri, 30 Mar 2018 18:23:43 GMT

This query should give you exact count of items having values in both indexes.
Even if there is duplication. Just to confirm you need count of items common or event count?
Event count will need some other approach.

Re: How to connect two different indexes by unique item code showing count / percent (with match) & (without match)?

timothytruax — Sun, 01 Apr 2018 23:45:30 GMT

Hey deepashri_123 -
I tried entering this one below; naturally with my fields and it did not work - it just shows a count = 0.

index=index1 OR index=index2 | rename item2 as item1 | stats count dc(index) AS dc by item1 | where dc>1 | stats count .

Any other suggestions? I continue to try different approaches.

Re: How to connect two different indexes by unique item code showing count / percent (with match) & (without match)?

somesoni2 — Mon, 02 Apr 2018 00:58:36 GMT

Try something like this

(index=index1 other parts ) OR (index=index2 other parts)
| eval itemCode=coalesce(item1,item2)
| stats dc(index) as reportedBy by itemCode
| stats count as Count by reportedBy | eventstats sum(Count) as Total
| eval Percent=round(Count*100/Total,2)
| eval ItemType=if(reportedBy=2,"Common in Both Indexes","Unique in Both Indexes")
| table ItemType Count Percent