how to replace join in this query?

[pstalin_]

Hi,

Anyone please help me in rewplacing join in this below query

index=168347-np [ | `last_np_sourcetype("index=168347-np","hardware")`] (physicalType=*) | fields physicalElementId deviceId
| join deviceId [ search index=168347-np [| `last_np_sourcetype( "index=168347-np", "group_members")` ] groupId=300543 | fields deviceId ]
| stats dc(physicalElementId) as Devices

[pstalin_]

Anyone please help me on this

[scelikok]

@pstalin_ , please try this;

index=168347-np 
    [| `last_np_sourcetype("index=168347-np","hardware")`] OR ( 
    [| `last_np_sourcetype( "index=168347-np", "group_members")` ] groupId=300543) 
| stats dc(physicalElementId) as Devices

[pstalin_]

@scelikok 

"index=168347-np [ | `last_np_sourcetype("index=168347-np","hardware")`] (physicalType=*) | fields physicalElementId deviceId
| join deviceId [ search index=168347-np [| `last_np_sourcetype( "index=168347-np", "group_members")` ] groupId=290681 | fields deviceId ]
| stats dc(physicalElementId) as Devices"

This queries contains the fields physicalElementId deviceId and deviceid has common but U didn't used this in your query I'm getting different answer.

[dmarling]

In order to properly answer this question we need to know how the "hardware" and "group_members" are being used in the last_np_sourcetype macro.  Is there a specific field where those values are being searched?  Is it just in the raw event somewhere?  If it is in the raw event then the below query would work but it's not as efficient as it would be if we knew the exact field that these values are expected in:

index=168347-np 
    ([| `last_np_sourcetype("index=168347-np","hardware")`] (physicalType=*)) OR
    ([| `last_np_sourcetype( "index=168347-np", "group_members")` ] groupId=290681)
| fields physicalElementId deviceId _raw
| stats values(_raw) as raw values(physicalElementId) as physicalElementId by deviceId
| search raw=*hardware* raw=*group_members*
| stats dc(physicalElementId) as Devices

[dmarling · Accepted solution]

I was informed that it's sourcetypes and not in the _raw.  Here's the adjusted solution:

index=168347-np 
    ([| `last_np_sourcetype("index=168347-np","hardware")`] (physicalType=*)) OR
    ([| `last_np_sourcetype( "index=168347-np", "group_members")` ] groupId=290681)
| fields physicalElementId deviceId sourcetype
| stats values(sourcetype) as sourcetype values(physicalElementId) as physicalElementId by deviceId
| search sourcetype=hardware sourcetype=group_members
| stats dc(physicalElementId) as Devices

[pstalin_]

@dmarling 

Hi,

I think its working thank you so much.

[dmarling]

I'm glad it's working.  Please mark the solution as accepted to help future individuals.  Thank you!