Dashboards & Visualizations

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

NamrataRathore
Explorer

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

Labels (2)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

0 Karma

NamrataRathore
Explorer

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.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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 🙂

0 Karma

NamrataRathore
Explorer

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?

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

NamrataRathore
Explorer

Hi @PickleRick , still not getting the TotalBytes column.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

NamrataRathore
Explorer

I am getting counts of 1 for all the hosts.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

NamrataRathore
Explorer

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

 

 

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...