Splunk Search

How can we add original fields after using stats count?

att35
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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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.

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...