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!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...