topic Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum? in Splunk Search

How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

BITSIntern — Mon, 28 Sep 2020 20:25:33 GMT

Hi guys,

So I need to figure out how to see if the thing from field ip_source equals the thing from field ip_destination and if it does, add the values of the two fields if the fields equal each other. Basically, I want the statistics to match up the items from each field and show their separate value and the values added together so that when I graph it in the visualization section there will be 3 different values (one for each field and one of the total of the 2 fields) for each ip address.

The 2 fields are: ip_source, ip_destination
They are in the same host and I am using all sources/sourcetypes (no specification)

Please let me know if you can help!

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

NOUMSSI — Tue, 30 Jun 2015 15:48:54 GMT

hi try this:

index=... source=you_source sourcetype=you_sourcetype host=same_host| stats c(ip_source) as count_ip_source c(ip_destination) as count_ip_destination|where ip_source=ip_destination | eval total= count_ip_source + count_ip_destination | table ip_source count_ip_source ip_destination count_ip_destination total

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

woodcock — Tue, 30 Jun 2015 15:52:12 GMT

I think this is what you need:

... | chart count(ip_source) count(ip_destination) count(eval(ip_source==ip_destination)) AS count_src_is_dest

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

stephanefotso — Tue, 30 Jun 2015 16:06:33 GMT

Hello! Try somethink like this:

index=... source=... sourcetype=...| stats values(ip_source ) as ip_source values(ip_destination ) as ip_destination|join [search index=... source=... sourcetype=... ip_source =* ip_destination =*|where ip_source =ip_destination |eval sumip=ip_source + ip_destination |table sumip]|table ip_source  ip_destination sumip

or this

 index=... source=... sourcetype=...ip_source =* ip_destination | eval sumip=case(ip_source =ip_destination ,ip_source +ip_destination )|stats values(ip_source ) as ip_source values(ip_destination ) as ip_dest values(sumip) as sumip

Thanks

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

BITSIntern — Tue, 30 Jun 2015 16:11:00 GMT

It's weird, so far, none have resulted in any output besides woodcock's and his only returned a total for all of the addresses (only one thing returned)

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

woodcock — Tue, 30 Jun 2015 16:26:39 GMT

The problem here is that you have definitely NOT been clear enough about what you desire. I can think of 3 totally different ways to understand what you wrote. It will REALLY help if you clarify with a more detailed example of data and desired results.

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

woodcock — Tue, 30 Jun 2015 16:32:43 GMT

Actually I think you are trying to get this:

...  | eval SameOrDifferent=if(ip_destination==ip_source),"SAME","DIFFERENT") | eval bothIPs = ip_source . "," . ip_destintation | makemv delim="," bothIPs | stats count AS each count(eval(SameOrDifferent=="SAME")) AS both by bothIPs | eval both=both/2

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

BITSIntern — Mon, 28 Sep 2020 20:25:42 GMT

uhh ok.... So for my ip_source there are many different ip addresses that have been repeated and the same goes for ip_destination. I would like to create a search that counts the total of each ip address which a top/rare limit search already does but I would like it to also match up the ip_source if it is identical (the ip address not the amount of times it has been repeated) to the ip_destination. If they are equal, it will count the total of the 2 different fields ( the ip_source and ip_destination) such that the one ip address will have three values: the ip_source count, the ip_destination count, the total count.

For mine, I don't have to specify the source/sourcetype, only the host.

Sorry if I was unclear, I am extremely new to splunk.

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

woodcock — Wed, 01 Jul 2015 14:06:17 GMT

Based on your clarification, I think this will do it:

... | where ip_destination=ip_source
| stats count AS both BY ip_source
| rename ip_source AS ip_address
| append [  ... | stats count AS source      BY ip_source      | rename ip_source      AS ip_address ]
| append [  ... | stats count AS destination BY ip_destination | rename ip_destination AS ip_address ]
| stats values(*) AS * by ip_address
| fillnull value=0

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

BITSIntern — Wed, 01 Jul 2015 14:41:35 GMT

Thank you this really helped!!

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

woodcock — Wed, 01 Jul 2015 14:44:44 GMT

When you ask questions better, you get better answers!

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

lancerkind — Tue, 29 Sep 2020 22:15:08 GMT

Sounds like you want to count the number of times ip_source is equal to ip_destination.

Re: How to search if a value from FieldA equals a value from FieldB, add the two values if true, and display both values with their sum?

algalvan — Tue, 01 Oct 2019 22:17:09 GMT

Is there a way to search where two fields equal each other before the first pipe?