topic Re: How to correlate fields value from different indexer? in Splunk Search

How to correlate fields value from different indexer?

syazwani — Tue, 08 Mar 2022 07:54:52 GMT

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.

Re: How to correlate fields value from different indexer?

gcusello — Tue, 08 Mar 2022 08:12:41 GMT

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

Re: How to correlate fields value from different indexer?

ITWhisperer — Tue, 08 Mar 2022 08:13:49 GMT

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)

Re: How to correlate fields value from different indexer?

syazwani — Tue, 08 Mar 2022 08:21:23 GMT

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. 😞

Re: How to correlate fields value from different indexer?

syazwani — Tue, 08 Mar 2022 08:25:05 GMT

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

Re: How to correlate fields value from different indexer?

ITWhisperer — Tue, 08 Mar 2022 08:26:16 GMT

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

Re: How to correlate fields value from different indexer?

PickleRick — Tue, 08 Mar 2022 09:00:13 GMT

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)