Splunk Search

How to get a calculated column in a table

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

0 Karma
1 Solution

xisura
Communicator

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

View solution in original post

niketn
Legend

@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

romoc
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

niketn
Legend

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

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

romoc
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

niketn
Legend

@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!!!"

romoc
Explorer

It worked like a charm! thanks @niketnilay!

0 Karma

xisura
Communicator

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

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

0 Karma

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

0 Karma

romoc
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:

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

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