Splunk Search

Basic use of tstats and a lookup

dmbr
Explorer

Here is a basic tstats search I use to check network traffic.

 

 

| tstats summariesonly=t fillnull_value="MISSING" count from datamodel=Network_Traffic.All_Traffic where All_Traffic.src IN ("11.2.2.1","11.2.2.2","11.2.2.3") by All_Traffic.src, All_Traffic.dest, All_Traffic.action, All_Traffic.dest_port, All_Traffic.bytes, sourcetype
| sort -count

 

 

I have a lookup file called "ip_ioc.csv" containing a single column of IPv4 addresses which constitute potential bad actors.  

Instead of searching through a list of IP addresses as per above, I want the tstats search to check the lookup file.

How can I modify the above search?

Here is a terrible and incorrect attempt at what I am trying to perform:

 

 

| tstats count from datamodel=Network_Traffic.All_Traffic by All_Traffic.src, All_Traffic.dest, All_Traffic.action, All_Traffic.dest_port, All_Traffic.bytes, sourcetype
| lookup ip_ioc.csv ip_ioc
| where ip_ioc == All_Traffic.src OR ip_ioc == All_Traffic.dest

 

 

 

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

Not so terrible, but incorrect 🙂 One way is to replace the last two lines with

| lookup ip_ioc.csv ip_ioc as All_Traffic.src OUTPUT ip_ioc as src_found
| lookup ip_ioc.csv ip_ioc as All_Traffic.dest OUTPUT ip_ioc as dest_found
| where !isnull(src_found) OR !isnull(dest_found)

looks like you want to check either src or dest, so you could possible use a subsearch in the tstats to pull in your IP addresses to be part of the where IN statement for each of src and dest, but the merits of each would be down to performance - the above is quite simple and easy to read.

View solution in original post

linwqg
New Member

Hello guys. 

Can i hitch on this to further check, how do i include timestamp for each match?

If i add by _time (in red below),  the output is automatically bucket.  If i specify span=1s, can i still pipe the result timechart span=1d? 

| tstats count from datamodel=Network_Traffic.All_Traffic by _time span=1s, All_Traffic.src, All_Traffic.dest, All_Traffic.action, All_Traffic.dest_port, All_Traffic.bytes, sourcetype

The desired output is for each match to carry _time, src, dst, ports fields, which can be used to generate timechart.

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Not so terrible, but incorrect 🙂 One way is to replace the last two lines with

| lookup ip_ioc.csv ip_ioc as All_Traffic.src OUTPUT ip_ioc as src_found
| lookup ip_ioc.csv ip_ioc as All_Traffic.dest OUTPUT ip_ioc as dest_found
| where !isnull(src_found) OR !isnull(dest_found)

looks like you want to check either src or dest, so you could possible use a subsearch in the tstats to pull in your IP addresses to be part of the where IN statement for each of src and dest, but the merits of each would be down to performance - the above is quite simple and easy to read.

dmbr
Explorer

Thank you! 😋 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...