Splunk Search

How to correlate fields value from different indexer?

syazwani
Path Finder

Hi, we would to correlate data between 2 idx, but we cant seem to find the right query.

Examples

Index= Firewall
Sourcetype = A
Field = Bytes, SourceIP

Index=AD
Sourcetype=B
Field=SourceIP, Hostname

We would like to calculate the byte in firewall index, and display the Hostname of SourceIP by correlating with AD index.

Here is example of our query which not work well.

(index=Firewall OR index=AD) sourcetype=A OR sourcetype=B
| eval TotalBandwidth = round((Bytes)/1024/1204,2)
| stats sum(TotalBandwidth) as "Total Bandwidth", latest(Hostname) as Hostname by SourceIP
| sort 10 - "Total Bandwidth"

When we run the above query, we able to display the as what we what, but some the result consist of unwanted Hostname. We tried to filter the Hostname by using | where Hostname!=" " ,  but the result is messed up. Other query that we have generate is;

 


(index=Firewall sourcetype=A SourceIP=* Bytes=*) OR (index=AD sourcetype=B SourceIP=* Hostname=*)
| fields index SourceIP Bytes SourceIP Hostname
| eval SourceIPNew=coalesce(SourceIP, SourceIP)
| eval TotalBandwidth = round((Bytes)/1024/1204,2)
| stats sum(TotalBandwidth) as "Total Bandwidth", values(Hostname) as Hostname by SourceIPNew  

This also not working ☹️. Please advise us. Thankyou. 

Labels (4)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

In what way is the Hostname unwanted/messed up?

(index=Firewall AND sourcetype=A) OR (index=AD AND sourcetype=B)
| eval TotalBandwidth = round((Bytes)/1024/1204,2)
| stats sum(TotalBandwidth) as "Total Bandwidth", latest(Hostname) as Hostname by SourceIP
| sort 10 - "Total Bandwidth"

Is it that the Hostname is not found in the AD index?

You could try adding

| where isnotnull(Hostname)
0 Karma

syazwani
Path Finder

Thankyou for the reply. We did try adding the | where isnotnull(Hostaname) , but then the Total Bandwidth column will be empty. Thats how the result messed up. 😞

0 Karma

PickleRick
Ultra Champion

A typical question when something that's supposed to be calculating OK returns an empty value - is your "Bytes" field (from which you're calculating the bandwidth further down the pipeline) a numeric field? If it is not, summing over it will not produce results and you have to firstly convert it to a number using something like

| eval numbytes=tonumber(Bytes)

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It sounds like your SourceIP addresses don't correlate - can you share some sample events from your indexes?

0 Karma

gcusello
Legend

Hi @syazwani,

your second search, for my knowledge is correct, I'd modify only a little thing:

(index=Firewall sourcetype=A SourceIP=* Bytes=*) OR (index=AD sourcetype=B SourceIP=* Hostname=*)
| stats sum(Bytes) AS TotalBandwidth values(Hostname) AS Hostname BY SourceIP 
| eval TotalBandwidth = round(TotalBandwidth/1024/1204,2)

Ciao.

Giuseppe 

0 Karma

syazwani
Path Finder

Thank you for the reply. Its still the same 😔. Could the field name (SourceIP) is same for both idx would be an issue?

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...