Basic use of tstats and a lookup

[dmbr]

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

[linwqg]

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.

[bowesmana · Accepted solution]

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]

Thank you! 😋