Splunk Enterprise

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

syazwani
Path Finder

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.

Labels (1)
Tags (3)
0 Karma
1 Solution

PickleRick
Ultra Champion

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.

View solution in original post

0 Karma

syazwani
Path Finder

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.

0 Karma

PickleRick
Ultra Champion

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.

0 Karma

syazwani
Path Finder

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.

0 Karma

PickleRick
Ultra Champion

Transaction is meant for something completely different.

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

0 Karma
Get Updates on the Splunk Community!

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...