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

[NamrataRathore]

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

[PickleRick]

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

[NamrataRathore]

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.

[PickleRick]

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 🙂

[NamrataRathore]

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?

[PickleRick]

Wait. You're running too quickly.

Do

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.

[NamrataRathore]

Hi @PickleRick , still not getting the TotalBytes column.

[PickleRick]

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.

[NamrataRathore]

I am getting counts of 1 for all the hosts.

[PickleRick]

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.

[NamrataRathore]

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