Archive

How to get a calculated column in a table

Explorer

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:
dbname tablename row_count
x a 4
x b 3
y a 4
y b 1

Report should look like this:

tablename x y recdiff
a 4 4 0
b 3 1 2

Any help will be very appreciated.

0 Karma
1 Solution

Communicator

|chart values(rowcount) over tablename by dbname |eval recdiff=x-y|table tablename * recdiff

View solution in original post

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Explorer

It worked like a charm! thanks @niketnilay!

0 Karma

Communicator

|chart values(rowcount) over tablename by dbname |eval recdiff=x-y|table tablename * recdiff

View solution in original post

Explorer

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 dbname and I'm not sure if function eval recdiff=x-y would work. This is a sample of 4 events captured:

2017-10-01 01:47:16.541, DBNAME="x", TABLENAME="a", SNAPDATE="2017-09-30 21:47:16.2", ROWCOUNT="183"
2017-10-01 01:42:36.069, DBNAME="y", TABLENAME="a", SNAPDATE="2017-10-01 01:42:35.0", ROWCOUNT="183"
2017-10-01 01:47:16.541, DBNAME="x", TABLENAME="b", SNAPDATE="2017-09-30 21:47:16.2", ROWCOUNT="1731"
2017-10-01 01:42:36.069, DBNAME="y", TABLENAME="b", SNAPDATE="2017-10-01 01:42:35.0", ROWCOUNT="1738"

I'll appreciate any help on this.
Thanks

0 Karma

Communicator

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(ROWCOUNT) over TABLENAME by DBNAME |eval RECDIFF=x-y|table TABLENAME x y RECDIFF

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.

0 Karma

Explorer

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:

DBNAME="x", TABLENAME="a", SNAPDATE="2017-09-30 21:17:16.267", ROWCOUNT="183"
DBNAME="y", TABLENAME="a", SNAPDATE="2017-10-01 01:12:35.0", ROWCOUNT="180"
DBNAME="y", TABLENAME="b", SNAPDATE="2017-09-30 21:17:16.267", ROWCOUNT="1731"
DBNAME="x", TABLENAME="b", SNAPDATE="2017-10-01 01:12:35.0", ROWCOUNT="1738"

I will appreciate any help.

Thanks

0 Karma