topic Re: Cannot sum two numbers in Splunk Search

Cannot sum two numbers

veromihaiu — Wed, 30 Sep 2020 02:24:38 GMT

I have the following problem: I have a variable "number_of_past_events" which comes from a "| inputlookup file.csv" and another variable from a sub search " nr_events". When I try to create a new variable with the sum of these two variables like this: "|eval new_number_of_events=number_of_past_events+nr_events " this new number does not have a value. I tried to use table command like this "| table number_of_past_events, nr_events,new_number_of_events" and the output shows the first two correctly but the new_number_of_events does not have a value. How can I resolve this problem?

Re: Cannot sum two numbers

gfreitas — Tue, 08 Oct 2019 10:37:20 GMT

It might be beneficial to get a screenshot of your fields from the interesting fields screen, but it seems one (or both) of the field(s) are not number. You could use

| convert num(wrongformatfield)

| eval tonumber(wrongformatfield)

More info here: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/convert and here https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/convert

Re: Cannot sum two numbers

veromihaiu — Tue, 08 Oct 2019 11:27:44 GMT

* LogName="Microsoft-Windows-PowerShell/Operational" earliest=-1000m latest=now
| search EventCode="4103" 
| chart 
count(eval(like(_raw,"%C:\Windows\system32\svchost.exe%"))) AS svchost_command  over _time span=20s 
| where (svchost_command>20) 
| eval message=if(svchost_command>20 ,"Detected","NOT DETECTED")
| append 
    [| inputlookup AvL_hist_test.csv ]
| append
    [search * LogName="Microsoft-Windows-PowerShell/Operational" earliest=-1000m latest=now
    | search EventCode="4103"
    | chart 
    count(eval(like(_raw,"%C:\Windows\system32\svchost.exe%"))) AS svchost_command  over _time span=20s
    | where (svchost_command>20) 
    | stats count(eval(svchost_command>20)) AS nr_events ]
| eval new_number_events=nr_events_history+nr_events
| table _time,message,nr_events_history,nr_events,new_number_events

Re: Cannot sum two numbers

veromihaiu — Wed, 30 Sep 2020 02:24:40 GMT

This is my entire code. The output shows the nr_events_history and nr_events as numbers but the new_number_events does not have a value

Re: Cannot sum two numbers

wmyersas — Tue, 08 Oct 2019 15:03:08 GMT

try adding a | fillnull to your outer and inner searches

Re: Cannot sum two numbers

gfreitas — Wed, 30 Sep 2020 02:25:22 GMT

I don't see any fields nr_events_history before you used it on the last eval (on the penultimate line). That might be the case, no?

Re: Cannot sum two numbers

veromihaiu — Thu, 10 Oct 2019 08:20:58 GMT

It found the solution...I used instead of append I used join for the subsearch...and it worked!

Re: Cannot sum two numbers

ololdach — Thu, 10 Oct 2019 08:34:56 GMT

Hi, so the reason that the numbers were not added wasn't the wrong format like we thought, it was that the two variables were not present in all events and the append was an attempt to add the missing values to your events. In that case it makes perfect sense to use join instead as it adds values to existing events rather than additional events at the bottom of the list.

Re: Cannot sum two numbers

veromihaiu — Thu, 10 Oct 2019 08:40:02 GMT

Yes, you are right! 🙂

Re: Cannot sum two numbers

veromihaiu — Wed, 30 Sep 2020 02:25:54 GMT

nr_events_history comes from the| inputlookup AvL_hist_test.csv. I found the problem: I had to use join instead of append for the subsearch.

Re: Cannot sum two numbers

ololdach — Thu, 10 Oct 2019 08:57:56 GMT

I looked at your code another time. The first append loads your historic value and outputs the nr_events_history field? If so, you could use a standard lookup command instead of the join. It's much faster. The second append/join adds the total number of events that match svchost_command>20? You could do the same with eventstats only difference being: you would not run the same query twice. As a last suggestion for optimisation: remove the if from the eval. The where makes sure that all events are "Detected" anyway. So, eval message="Detected " delivers the same result.

Re: Cannot sum two numbers

veromihaiu — Thu, 10 Oct 2019 09:04:04 GMT

Thank you for your suggestions! The response for your first question is yes, the first appends the historic value. The second adds the total number of events that matches svchost_command>20,yes. I will make the changes definitely! Thank you very much. 🙂