Splunk Search

Substract values of two columns and display the difference in third column?

rashidjorvee
New Member

I have a table which have fields Rank, City, Population _2001, Population _2011. Now I want to find the growth in population for respective cities. I try fetching the growth with "eval growth=P2011-P2001", but it didn't work. Please suggest some way to achieve this.

Search Query: index="rashid" City=A* AND "Population _2001">="100000" | table Rank, City, "Population _2001", "Population _2011" |rename "Population _2001" as P2001|rename "Population _2011" as P2011| eval growth=P2011-P2001

alt text

0 Karma
1 Solution

damien_chillet
Builder

I think the problem is the values are seen as strings because of commas,
add the following before your eval to convert strings to numbers:

| convert num(P2001) num(P2011)

View solution in original post

ssadanala1
Contributor

The reason why the values are not subtracted because of "," in the number make splunk to consider them as string and unable to do the caluclation .

For this we need to convert the fields to number so that "," will be removed and then eval diff = p1 - p2

For example

|makeresults |eval p1 = "2,000;1,000" |eval p2= "10,00;2,000" | eval p1 = split(p1,";") |eval p2 = split(p2, ";") |mvexpand p1 |mvexpand p2 |convert num(p*) |eval diff = p1-p2

rashidjorvee
New Member

Thank you for your explanation. @ssadanala1
When I converted that field in a number those commas [,] has been removed and then my query is working fine and returning expected result.

0 Karma

damien_chillet
Builder

I think the problem is the values are seen as strings because of commas,
add the following before your eval to convert strings to numbers:

| convert num(P2001) num(P2011)

damien_chillet
Builder

Also, worth saying that your filter >= 100000 does not seem to work because Aurangabad is in the results and has 79,393 inhabitants.
You will have to create a calculated field to remediate that.

0 Karma

rashidjorvee
New Member

Yes, this filter is not working as well.

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 ...