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!

Splunk App for Anomaly Detection End of Life Announcment

Q: What is happening to the Splunk App for Anomaly Detection?A: Splunk is officially announcing the ...

Aligning Observability Costs with Business Value: Practical Strategies

 Join us for an engaging Tech Talk on Aligning Observability Costs with Business Value: Practical ...

Mastering Data Pipelines: Unlocking Value with Splunk

 In today's AI-driven world, organizations must balance the challenges of managing the explosion of data with ...