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 :frowning_face:. 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. :disappointed_face:

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

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 :pensive_face:. Could the field name (SourceIP) is same for both idx would be an issue?

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...