topic Re: Sum of two fields from different searches in Splunk Search

How to get Sum of two fields from different searches?

Techie — Mon, 18 Sep 2023 22:03:02 GMT

Hi -

I would like to join and sum the results and output

The searches:

index=test_index sourcetype="test_source" className=export | table message.totalExportedProfileCounter

index=test_index sourcetype="test_source" className=export | table message.exportedRecords

From above both searches I am looking to add message.totalExportedProfileCounter, message.exportedRecords. For a given call only one of the above search shows up.

I am looking for message.totalExportedProfileCounter + message.exportedRecords

Thanks in advance!

Thanks.

Re: Sum of two fields from different searches

ITWhisperer — Sat, 16 Sep 2023 07:28:51 GMT

Calculations can be done with fields in the same event.

index=test_index sourcetype="test_source" className=export | eval total = message.totalExportedProfileCounter + message.exportedRecords

If these fields do not have values in the same event, you need to use something like stats to correlate different events into the same event. For this you need a common field value between events to correlate them by.

Re: Sum of two fields from different searches

Techie — Sat, 16 Sep 2023 14:55:34 GMT

@ITWhisperer Thanks for the reply,

I tried below individually for getting sum of all records for each event type

index=test_index sourcetype="test_source" className=export | stats sum(message.totalExportedProfileCounter) as Total_number_of_exported_profiles

index=test_index sourcetype="test_source" className=export | stats sum(message.exportedRecords) as Total_number_of_exported_profiles

Above queries run just fine by themselves but I am more interested to add both these results into one.

Also the common field that you were asking for can be message.type=export_job which is available in both events.

Re: Sum of two fields from different searches

ITWhisperer — Sat, 16 Sep 2023 16:09:30 GMT

Is this what you mean?

index=test_index sourcetype="test_source" className=export | stats sum(message.totalExportedProfileCounter) as Total_number_of_exported_profiles sum(message.exportedRecords) as Total_number_of_exported_records | eval total = Total_number_of_exported_profiles + Total_number_of_exported_records

Re: Sum of two fields from different searches

Techie — Sat, 16 Sep 2023 16:55:04 GMT

This is not helping.

Total_number_of_exported_profiles or Total_number_of_exported_records is showing up but not sum of them. See below screenshot.

Re: Sum of two fields from different searches

ITWhisperer — Sat, 16 Sep 2023 17:47:07 GMT

index=test_index sourcetype="test_source" className=export | stats sum(message.totalExportedProfileCounter) as Total_number_of_exported_profiles

give you a result, and

index=test_index sourcetype="test_source" className=export | stats sum(message.exportedRecords) as Total_number_of_exported_records

also gives you a result, then

should give you two results which can be added together. Please recheck your searches.

Re: Sum of two fields from different searches

Techie — Sat, 16 Sep 2023 22:16:42 GMT

Looks like the final sum is not calculated when one of the results is empty. If both are available then the total is populated correctly. In my case either one of them is present. Any idea how to calculate sum in this case?

| eval total = Total_number_of_exported_profiles + Total_number_of_exported_records

Re: Sum of two fields from different searches

ITWhisperer — Sun, 17 Sep 2023 08:16:30 GMT

| fillnull value=0 Total_number_of_exported_profiles Total_number_of_exported_records | eval total = Total_number_of_exported_profiles + Total_number_of_exported_records

Re: Sum of two fields from different searches

Techie — Mon, 18 Sep 2023 15:10:16 GMT

This helped. Thanks @ITWhisperer