How to get the count of fields from 2 database tables that have one field in common

[isha_rastogi]

I have a table that counts different versions of products and a second table that has the system type, like Laptop, VDI, etc. I can combine both tables by ID, but as it's unique I don't need this individually.

I need to create a chart something like below

Product Version System count
1.1             20
1.2             13
1.3             17

I'm writing the search like this:

earliest=-24h latest=now index=abc  source=xyz| dedup ID |stats values(RUNNING_VER) as Version | mvexpand Version |join [search earliest=-24h latest=now index=abc source=pqr  | dedup NAME|eval Names=substr(NAME,1,3)|rex field=Names "(?.$)"| search Systems="I" OR Systems="X"|stats count by Systems] 

I'm always getting same count if I use this search. Any help would be greatly appreciated.

[maciep]

I get the impression that you may not know how join works. Typically, you use join to glue to result sets together by one or more fields. It looks like your search is trying to join a result set of just one field called Version to a result set of just two fields called count and Systems. So there's no field for splunk to use to join those together?

Anyway, you may be able to get away with just stats and not use join. Note: it's really hard to come up with a working search without actually seeing your data, but maybe something like this.

earliest=-24h latest=now index=abc (source=xyz OR (source=pqr AND (Systems="I" OR Systems="X")))  
| stats count(Systems) as system_count, values(RUNNING_VER) as product_version by ID 
| stats sum(system_count) by product_version

So get data from both sources initially (assuming the Systems filter is needed for pqr from your search?) . Then use stats to count events from the pqr data and get version from xyz source by the common ID field. And since you may have duplicate versions across IDs (???), just sum the counts from the pqr source by version from xyz.

Hope that helps somewhat.

[isha_rastogi]

I got it working with join. was missing stats command after joining tables. Thanks for your help. I'll try your solution as well it seems like optimized one