Splunk Search

Using join statement with count and dedup

cpeteman
Contributor

I have the current statement using append:

search_term1 | stats count by ip_address | table ip_address count | append [search search_term1 | dedup ip_address | table ipaddress _raw]

which makes a table rows:

ip_address---------- count ------------ _raw

123.456.1.1 -------- 520 ------------------

123.456.1.1 ----------------------------- raw data

I would like to combine my data into single lines:

ip_address---------- count ------------ _raw

123.456.1.1 -------- 520 -------------- raw data

It seems that I should use the join statement but when I do the raw data refuses to display at all. Please help! Thanks!

1 Solution

cpeteman
Contributor

I was able to solve this by using selfjoin statement:

search_term1 | stats count by ip_address | rename ip_address as sip_address | rename count as scount | table sip_address,scount | append [ search search_term1 | dedup ip_address | rename ip_address as sip_address | table sip_address,_raw ] | selfjoin sip_address

View solution in original post

cpeteman
Contributor

I've had to do a fair bit more on this stuff since I asked so I may have a shot at helping

0 Karma

cpeteman
Contributor

If anyone need help with a problem similar to this feel free to comment.

0 Karma

cpeteman
Contributor

I was able to solve this by using selfjoin statement:

search_term1 | stats count by ip_address | rename ip_address as sip_address | rename count as scount | table sip_address,scount | append [ search search_term1 | dedup ip_address | rename ip_address as sip_address | table sip_address,_raw ] | selfjoin sip_address

cpeteman
Contributor

Although I would still like to know why it is that count must be renamed.

Get Updates on the Splunk Community!

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...

[Live Demo] Watch SOC transformation in action with the reimagined Splunk Enterprise ...

Overwhelmed SOC? Splunk ES Has Your Back Tool sprawl, alert fatigue, and endless context switching are making ...

What’s New & Next in Splunk SOAR

Security teams today are dealing with more alerts, more tools, and more pressure than ever.  Join us on ...