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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...