topic Re: How to perform arithmetic operations on data fetched from csv file in Dashboards & Visualizations

How to perform arithmetic operations on data fetched from csv file?

NamrataRathore — Tue, 08 Mar 2022 17:51:36 GMT

I have an index which has information for available bytes on each host. I want to display free bytes in a table for all the hosts. I have a csv file which has the hostname and the total bytes for each server.

I am trying something like below:

index="perfmon" ([| from inputlookup:"HostTotalBytes_Lookup" | table host]) earliest=-5m latest=now | eval TotalBytes=[| from inputlookup:"HostTotalBytes_Lookup" | table totalBytes] | eval MemoryUsedPct = round((TotalBytes - Available_Bytes) / TotalBytes * 100, 2) | chart max(MemoryUsedPct) as "Used Memory", max(Available_Bytes) as "Available Bytes" by host

But it returns an error:
Error in 'eval' command: Fields cannot be assigned a boolean result. Instead, try if([bool expr], [expr], [expr]).

I understand that I am trying to store an entire table in one variable and trying to use it as separate bytes against each host. I am quite new to Splunk and don't really know what to use in this case. Any help would be highly appreciated.

My csv file looks like the following:

host,name,totalBytes
host1,host1_name,16000000000
host2,host2_name,16000000000
host3,host3_name,16000000000
host4,host4_name,16000000000

Re: How to perform arithmetic operations on data fetched from csv file

PickleRick — Tue, 08 Mar 2022 11:08:50 GMT

Lookup is just for that - looking up values by field. So if you want to get a value from your lookup matching a host field, you simply do

<your_search> | lookup host OUTPUT totalBytes AS TotalBytes

See https://docs.splunk.com/Documentation/Splunk/8.2.5/SearchReference/Lookup

Re: How to perform arithmetic operations on data fetched from csv file

NamrataRathore — Mon, 21 Mar 2022 19:04:24 GMT

Hi @PickleRick

My apologies for getting back to you after a while.

What you suggested did not work for me. Please see the updated query below:
index=perfmon ([| from inputlookup:"HostTotalBytes_Lookup" | table host]) earliest=-5m latest=now sourcetype=PerfmonMk:Memory OR (sourcetype=Perfmon:Memory counter="Available Bytes")
| eval AvailableBytes=if(isnotnull(Available_Bytes),Available_Bytes,Value)
| lookup "HostTotalBytes_Lookup" host OUTPUT totalBytes AS TotalBytes
| eval MemoryUsedPct = round((TotalBytes - AvailableBytes) / TotalBytes * 100, 2)
| chart max(Available_Bytes) as "Available Bytes", max(totalBytes) as "Total Bytes", max(MemoryUsedPct) as "Used Memory" by host

This eliminated the error that I was getting earlier but it didn't return the desired result.

Re: How to perform arithmetic operations on data fetched from csv file

PickleRick — Mon, 21 Mar 2022 19:19:53 GMT

In such cases, it's best not to just try the whole search and test if it works or not but see if each subsequent step produces desired results.

So end at lookup command and see if it properly adds the TotalBytes column to your results. If so, then you have to check what's preventing splunk from calculating the stats (maybe the field is getting interpreted as a string one - then you won't be able to do arithmetics on it without casting it to number). If not, then check your lookup. And so on. Baby steps 🙂

Re: How to perform arithmetic operations on data fetched from csv file

NamrataRathore — Tue, 22 Mar 2022 04:16:48 GMT

Hi @PickleRick

The below query does not work:
index=perfmon ([| from inputlookup:"IM_prod_hosts_Lookup" | table host])
| lookup "IM_Prod_HostTotalBytes_Lookup" host OUTPUT totalBytes AS TotalBytes
| chart max(TotalBytes) as "Total Bytes" by host

However, the below one shows the total bytes:
| from inputlookup:"IM_Prod_HostTotalBytes_Lookup" | table host, totalBytes

What do you think am I missing in my query?

Re: How to perform arithmetic operations on data fetched from csv file

PickleRick — Tue, 22 Mar 2022 07:31:03 GMT

Wait. You're running too quickly.

index=perfmon ([| from inputlookup:"IM_prod_hosts_Lookup" | table host])
| lookup "IM_Prod_HostTotalBytes_Lookup" host OUTPUT totalBytes AS TotalBytes

And check if you're getting the TotalBytes column populated.

Re: How to perform arithmetic operations on data fetched from csv file

NamrataRathore — Tue, 22 Mar 2022 08:54:20 GMT

Hi @PickleRick , still not getting the TotalBytes column.

Re: How to perform arithmetic operations on data fetched from csv file

PickleRick — Tue, 22 Mar 2022 09:10:58 GMT

Which means that the lookup isn't working.

The question is why.

In theory, if you're getting results from

| from inputlookup:"IM_Prod_HostTotalBytes_Lookup" | table host, totalBytes

You should get results from

| lookup "IM_Prod_HostTotalBytes_Lookup" host OUTPUT totalBytes AS TotalBytes

It's the same lookup (otherwise you'd get error that the lookup doesn't exist), you're looking up by the host field which is supposed to exist in the lookup.

Maybe there's an issue with the values of the host field? Maybe you have some whitespace in your lookup which confuse the lookup mechanism?

You could try verifying it with something like

index=perfmon ([| from inputlookup:"IM_prod_hosts_Lookup" | table host])
| stats values(host) as host
| mvexpand host| append [ | from inputlookup:"IM_Prod_HostTotalBytes_Lookup"
     | stats values(host) as host     | mvexpand host ]| stats count by host

And see whether you get counts of 1 or 2 over your host values.

Re: How to perform arithmetic operations on data fetched from csv file

NamrataRathore — Tue, 22 Mar 2022 09:30:51 GMT

I am getting counts of 1 for all the hosts.

Re: How to perform arithmetic operations on data fetched from csv file

PickleRick — Tue, 22 Mar 2022 09:51:51 GMT

OK, now sort the results by the host column and see.

If you have single values - that means you get completely different results from your base search and you have different hosts defined in the lookup.

If however you have pairs of "the same" host values, each with count of 1, that probably means that the values seem the same but differ in some way - most probably the lookup values contain some whitespaces.

Re: How to perform arithmetic operations on data fetched from csv file

NamrataRathore — Tue, 22 Mar 2022 18:38:17 GMT

Hi @PickleRick,

I have checked for whitespaces in the lookup but couldn't find any.

Below query runs perfectly fine for me:

| from inputlookup:"IM_Prod_HostTotalBytes_Lookup" | table host
| lookup "IM_Prod_HostTotalBytes_Lookup" host OUTPUT totalBytes AS TotalBytes

But not when ran as subquery:

index=perfmon ([| from inputlookup:"IM_Prod_HostTotalBytes_Lookup" | table host])
| lookup "IM_Prod_HostTotalBytes_Lookup" host OUTPUT totalBytes AS TotalBytes