Splunk Search

How to pre-calculate and search historical data from correlation between index and CSV/DB lookup?

LearningGuy
Motivator

Hello,
How to pre-calculate and search historical data from correlation between index and CSV/DB lookup?

For example:
From vulnerability_index, there are 100k of IP addresses scanned in 24 hours. When performing a lookup on CSV file from this index, only 2 IPs matches, but every time a search is performed in dashboard, it compares 100k IPs with 2 IPs.

How do we pre-calculate the search and store the data, so every time a search is performed on a dashboard, it only search for the historical data and it does not have to compare 100k IPs with IPs?

Thank you in advanced for your help

| index=vulnerability_index
| table ip_address, vulnerability, score

ip_address        vulnerability                       score
192.168.1.1SQL Injection9
192.168.1.1OpenSSL7
192.168.1.2Cross Site-Scripting      8
192.168.1.2DNS5
x.x.x.x ...
total IP:100k  

 

company.csv

ip_address      

company

location

192.168.1.1

Comp-A       

Loc-A

192.168.1.2

Comp-B

Loc-B

 

| lookup company.csv ip_address as ip_address OUTPUTNEW ip_address, company, location

ip_addressvulnerabilityscorecompanylocation
192.168.1.1SQL Injection9Comp-ALoc-A
192.168.1.1OpenSSL7Comp-ALoc-A
192.168.1.2Cross Site-Scripting8Comp-BLoc-B
192.168.1.2DNS5Comp-BLoc-B

 

Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Something like

index=vulnerability_index
    [| inputlookup company.csv
    | stats values(ip_address) as ip_address]
| table ip_address, vulnerability, score
| lookup company.csv ip_address as ip_address OUTPUTNEW ip_address, company, location

Hope this helps

LearningGuy
Motivator

Hello,
Thank you for your suggestion.
On your suggestion, when using index=vulnerability_index,
Doesn't the search still correlate 100k IPs with the CSV?

Is it possible to create a historical data or index or DB to bypass the original vulnerability index?
For example:
index=new_vulnerability_index

The old vulnerability_index has total 100k IPs, but this new index only has 2 IPs and 4 rows because it's already pre-calculated

ip_address        vulnerability                       score
192.168.1.1SQL Injection9
192.168.1.1OpenSSL7
192.168.1.2Cross Site-Scripting      8
192.168.1.2DNS5
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...