How to correlate fields value from different indexer?

[syazwani]

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. 

[ITWhisperer]

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)

[syazwani]

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

[PickleRick]

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)

[ITWhisperer]

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

[gcusello]

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 

[syazwani]

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