How can we add original fields after using stats count?

[att35]

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

[bowesmana]

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

[scelikok]

Hi @att35,

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.