Splunk Search

How can we add original fields after using stats count?

abhijittikekar
Builder

Hi,

We have a use-case where responses(host_addr) returned from DNS queries are passed through AbuseIPDB API to check for any potential matches.  Since the API has a set limit we dont want to query an IP more than once. To achieve this, stats is used to get distinct values and then it is passed through the API. It works well but due to the use of "stats", we lose all the other crucial fields from the original data, e.g. src_ip, query etc. Here's a sample query:

<Base Search> | stats count by host_addr |  table host_addr |  abuseip ipfield=host_addr | sort - AbuseConfidence

Could eventstats come to the rescue here? If so, what could be a potential syntax of that search? From the other examples I saw, eventstats sees to be more useful when performing a actual stats function like sum etc.

End goal is to create something like

| table src_ip, query, host_addr, LastReportedAt, AbuseConfidence

 but also keeping API limits in check(Using only unique values of host_addr).

Any pointers on this will be appreciated.

Thanks,

~ Abhi

 

Labels (2)
Tags (2)
0 Karma

bowesmana
Champion

eventstats doesn't seem likely as the abuse API seems to only take a single IP address.

A couple of possibilities, one to use join, so in the subsearch you can do the base search and the lookups as you are currently doing and then the outer search will join with the subsearch on host address, but join is not a good solution as there are resource limits you may hit.

An alternative, which may work could be to run an appendpipe subsearch. The difference with that is that the appendpipe subsearch is run after the main search. In that search, you could do the stats/abuseip calls and then a further stats after that which merges the values from the appendpipe into their corresponding row, something like

<Base Search> 
| stats count by src_ip, query, host_addr 
| appendpipe [
  | stats count by host_addr
  | abuseip ipfield=host_addr 
]
| sort - host_addr
| filldown query src_ip
| stats values(LastReportedAt) as LastReportedAt values(AbuseConfidence) as AbuseConfidence by src_ip, query, host_addr
0 Karma

scelikok
Champion

Hi @abhijittikekar,

You can use values() function in stats to keep the required values for table. Please try below search; (I assume LastReportedAt field returns from abuseip command.)

<Base Search> 
| stats values(src_ip) as src_ip values(query) as query by host_addr 
| abuseip ipfield=host_addr 
| sort - AbuseConfidence
| table src_ip, query, host_addr, LastReportedAt, AbuseConfidence

 

If this reply helps you an upvote is appreciated.

If this reply helps you an upvote is appreciated.
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!