topic How to get a calculated column in a table in Splunk Search

How to get a calculated column in a table

romoc — Tue, 29 Sep 2020 16:03:45 GMT

Hi Splunk Experts,
I need to create a report to display the table record count difference between two databases during a period of time.

Events (list) are captured as follow:
db_name table_name row_count
x a 4
x b 3
y a 4
y b 1

Report should look like this:

table_name x y rec_diff
a 4 4 0
b 3 1 2

Any help will be very appreciated.

Re: How to get a calculated column in a table

xisura — Tue, 29 Sep 2020 16:00:06 GMT

|chart values(row_count) over table_name by db_name |eval rec_diff=x-y|table table_name * rec_diff

Re: How to get a calculated column in a table

niketn — Mon, 02 Oct 2017 03:12:59 GMT

@romoc, does your table always have 4 rows? Will it have only two rows for specific table_name one for each x and y? If not please add more sample data and correlation. If so following answer by @xisura should work.

Re: How to get a calculated column in a table

romoc — Mon, 02 Oct 2017 16:57:54 GMT

Hi Niketnilay,
That was just a sample of records - there would be more tables to compare and the list could grow over the time. What is a fact, there would be always two databases to compare - x and y in this case.

Re: How to get a calculated column in a table

romoc — Tue, 29 Sep 2020 16:00:16 GMT

Thanks Xisure - I tried your suggestion but it get no results found. By the way, x and y are the possible values for field db_name so, I'm not sure if the eval function is expressed correctly. For example, this is in fact how some events (from all the list of possible events) look like:

DB_NAME="x", TABLE_NAME="a", SNAP_DATE="2017-09-30 21:17:16.267", ROW_COUNT="183"
DB_NAME="y", TABLE_NAME="a", SNAP_DATE="2017-10-01 01:12:35.0", ROW_COUNT="180"
DB_NAME="y", TABLE_NAME="b", SNAP_DATE="2017-09-30 21:17:16.267", ROW_COUNT="1731"
DB_NAME="x", TABLE_NAME="b", SNAP_DATE="2017-10-01 01:12:35.0", ROW_COUNT="1738"

I will appreciate any help.

Thanks

Re: How to get a calculated column in a table

niketn — Mon, 02 Oct 2017 17:25:47 GMT

If that is the case, the answer by @xisura should work. Please try out and confirm.

Re: How to get a calculated column in a table

romoc — Tue, 29 Sep 2020 16:00:19 GMT

Thanks xisura - I tried your suggestion but unfortunately I get "no results found". I want to clarify also that x and y are the two possible values for db_name and I'm not sure if function eval rec_diff=x-y would work. This is a sample of 4 events captured:

2017-10-01 01:47:16.541, DB_NAME="x", TABLE_NAME="a", SNAP_DATE="2017-09-30 21:47:16.2", ROW_COUNT="183"
2017-10-01 01:42:36.069, DB_NAME="y", TABLE_NAME="a", SNAP_DATE="2017-10-01 01:42:35.0", ROW_COUNT="183"
2017-10-01 01:47:16.541, DB_NAME="x", TABLE_NAME="b", SNAP_DATE="2017-09-30 21:47:16.2", ROW_COUNT="1731"
2017-10-01 01:42:36.069, DB_NAME="y", TABLE_NAME="b", SNAP_DATE="2017-10-01 01:42:35.0", ROW_COUNT="1738"

I'll appreciate any help on this.
Thanks

Re: How to get a calculated column in a table

romoc — Mon, 02 Oct 2017 17:33:02 GMT

Hi Niketnilay - I just replied @xisura. I'm not getting the expected results yet and provided further details. Hope you could have any other suggestion.

Re: How to get a calculated column in a table

niketn — Mon, 02 Oct 2017 17:57:24 GMT

@romoc, field names are case sensitive in Splunk. In your question you had tabular data with lower case field names. However, in the raw events you seem to have upper case field names. So try the following:

| chart first(ROW_COUNT) as ROW_COUNT over TABLE_NAME by DB_NAME
| eval REC_DIFF=y-x
| table TABLE_NAME x y REC_DIFF

Following is a run anywhere search which mocks up data similar to your example above and then performs the chart function as

| makeresults
| eval data="x,a,183;x,b,1781;y,a,183;y,b,1783;x,c,150;x,d,1780;y,c,151;y,d,1785;"
| makemv delim=";" data 
| mvexpand data
| eval data=split(data,",")
| eval DB_NAME=mvindex(data,0)
| eval TABLE_NAME=mvindex(data,1)
| eval ROW_COUNT=mvindex(data,2)
| table DB_NAME TABLE_NAME ROW_COUNT
| sort TABLE_NAME DB_NAME
| chart first(ROW_COUNT) as ROW_COUNT over TABLE_NAME by DB_NAME
| eval REC_DIFF=y-x
| table TABLE_NAME x y REC_DIFF

PS: Commands till | sort TABLE_NAME DB_NAME generates the mock data for testing.

Re: How to get a calculated column in a table

romoc — Mon, 02 Oct 2017 18:05:52 GMT

It worked like a charm! thanks @niketnilay!

Re: How to get a calculated column in a table

xisura — Tue, 29 Sep 2020 16:00:34 GMT

Hi @romoc,

I tried to simulate your case, I index the sample data you gave since there are key pair value which splunk will auto extract for you in search time , i dont need to manually extract it. So I run my search

index="test" sourcetype="test2" source="/home/Documents/test2.txt" |chart values(ROW_COUNT) over TABLE_NAME by DB_NAME |eval REC_DIFF=x-y|table TABLE_NAME x y REC_DIFF

and it works 🙂

note: I use the "*" on my first answer inside the table so i twill show all the values under DB_NAME in order also values are not static.