how to replace join?

[priyastalin]

Hi,  @493669  @MuS  @dturnbull_splun  @bowesmana 

Anyone please help me in replacing join in the below query??

" index=167515-np sourcetype=hardware
| fields deviceId, productType, productId, physicalType
| search physicalType=Chassis
| dedup deviceId
| join deviceId [ search index=167515-np [| `last_np_sourcetype( "index=167515-np", "group_members")` ] groupId=288348 | fields deviceId ]
| stats dc(productId) as PIDs by productType
| search productType=Routers
| table PIDs"

Thanks

[to4kawa]

fixed sample:

(index=167515-np sourcetype=hardware physicalType=Chassis) OR (index=167515-np 
    [| `last_np_sourcetype( "index=167515-np", "group_members")` ] groupId=288348 )
| fields deviceId, productType, productId, physicalType, sourcetype 
| stats values(*) as * dc(sourcetype) as flag by deviceId
| where flag > 1
| stats dc(productId) as PIDs by productType 
| search productType=Routers 
| table PIDs

[priyastalin]

Hi @to4kawa ,

I didn't get output for this, Could you please help me in solving this?

[to4kawa]

I made a mistake and fixed it.

[priyastalin]

Hi @to4kawa ,

Couldn't see your solution can you send me again?

Thanks