Is there a better accelerator command that can help to correlate data?

[syazwani]

Hello peeps,

Does anyone know a better accelerator command that can help to correlate data? Im trying to correlate proxy server logs and AD logs. 

Please see my base search;

(index=proxy OR index=ad) src_ip!="-"
| transaction src_ip
| eval MB=round(((bytes_in+bytes_out)/1024/1024),2)
| stats sum(MB) as "Bandwidth", values(WorkstationName) as Hostname by src_ip
| sort 10 - Bandwidth
| rename src_ip as "Source IP"

Please help me to sort out this issue. Thank you.

[syazwani]

Thanks PickleRick for your reply!

Im using transaction command as I need to combine the same field from different index.

For example;

index=proxy   --> src_ip,  src

index=ad --> src_ip, src

I need to correlate this src_ip field from index=proxy with src_ip field from index=ad to get the value of Workstation field.

Is there any command i can use to extract the Workstation value instead of transaction command?

Please advise.

[PickleRick · Accepted solution]

That's what groupping in stats is for. So your approach to do

| stats values(whatever) by src_ip

was right.

The only problem is that if you have overlapping fields in different sourcetypes they can produce kinda pointless results after aggregating. So you might want to "split" the field into two different fields so that after aggregation it still makes sense.

For example

(index=A or index=B) 
| eval Asrc=if(index="A", src, null())
| eval Bsrc=if(index="B", src, null())

In some cases you might need to use join command to do a DB-like join between two different result sets but due to performance reasons it's best not to use it if you can avoid it and try to do your "joining" with stats.

[syazwani]

Got it! Ive removed the transaction command and just leave the stats command. It works. Thank you so much for your explanation. Really appreciate it.

[PickleRick]

Transaction is meant for something completely different.

It looks like you only need to do the stats here.