Stats with Join

[doppiolover]

I have set of hosts that are installed with different versions of software but logging to the same index, and I need to create a report based on specific versions. Here is an example set of data that I have to deal with.

host	msgCode	msgText	qty
A	101	1.0.0
A	103	Nut	48
A	103	Widget	289
B	101	1.1.0
B	103	Nut	69
B	103	Widget	367
C	101	1.0.0
C	103	Nut	93
C	103	Widget	433
D	101	1.0.3
D	101	Nut	74
D	103	192
E	101	1.0.0
E	103	Nut	88
E	103	Widget	225

Given this set of data, I would like to get the sum of qty for the hosts that have msgCode=1.0.0. The caveat on this is that msgCode 101 is logged sporadically every couple of days, and when the host gets updated with the newer version,  so I only want to use the most recently logged value as is the case with host B. As such, the result should be something like the following which only sum qty values from hosts A, C E:

Nut	229
Widget	947

I can do sum of all qty, but I am not sure how I can filter out host B from it. I was doing something like the following, but then, I just get the result from the sub query.  

index=myIndex 
| eventstats sum(qty) by msgCode as partSum 
| table msgCode, partSum 
| join host type=inner [ search index=myIndex msgCode=101 msgText=1.0.0 earliest=-3d | stats count by host ]

[ITWhisperer]

Assuming there is a typo in your example (host D Nut should be msgCode 103?)

| eval version=if(msgCode="101",msgText,null)
| eventstats values(version) as version by host
| where msgCode!="101" AND version="1.0.0"
| stats sum(qty) as total by msgText

[doppiolover]

Thanks. Yes, that's a typo since I was making up the data. I will play around with your suggestion.