How to get a calculated column in a table

[romoc]

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.

[niketn]

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

[romoc]

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.

[niketn]

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

[romoc]

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.

[niketn]

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

[romoc]

It worked like a charm! thanks @niketnilay!

[xisura · Accepted solution]

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

[romoc]

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

[xisura]

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.

[romoc]

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