Splunk Search

how to replace join in this query?

pstalin_
Engager

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

Labels (1)
Tags (1)
0 Karma
1 Solution

dmarling
Builder

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
If this comment/answer was helpful, please up vote it. Thank you.

View solution in original post

0 Karma

pstalin_
Engager

Anyone please help me on this

Tags (1)
0 Karma

scelikok
SplunkTrust
SplunkTrust

@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

 

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

pstalin_
Engager

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

0 Karma

dmarling
Builder

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
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

dmarling
Builder

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
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

pstalin_
Engager

@dmarling 

Hi,

I think its working thank you so much.

0 Karma

dmarling
Builder

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

If this comment/answer was helpful, please up vote it. Thank you.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...