topic Re: how to replace join in this query? in Splunk Search

how to replace join in this query?

pstalin_ — Mon, 14 Dec 2020 11:30:09 GMT

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

Re: how to replace join in this query?

pstalin_ — Mon, 14 Dec 2020 13:35:51 GMT

Anyone please help me on this

Re: how to replace join in this query?

scelikok — Mon, 14 Dec 2020 18:30:10 GMT

@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

Re: how to replace join in this query?

pstalin_ — Tue, 15 Dec 2020 04:08:46 GMT

@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.

Re: how to replace join in this query?

dmarling — Tue, 15 Dec 2020 13:29:39 GMT

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

Re: how to replace join in this query?

dmarling — Tue, 15 Dec 2020 13:59:59 GMT

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

Re: how to replace join in this query?

pstalin_ — Wed, 16 Dec 2020 03:59:31 GMT

@dmarling

Hi,

I think its working thank you so much.

Re: how to replace join in this query?

dmarling — Wed, 16 Dec 2020 05:41:09 GMT

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