Splunk Search

Format or Splitup a row of data ito three rows for reporting

hartfoml
Motivator

I am doing a search from two databases and comparing data from both. I am using the appenccols command to get the data from both databases in one row so that I can do math on the data like this.

[| dbquery "db1" "SELECT" | join type left [dbquery "db2" "SELECT"]| fields db1_count notindb2 | appenccols [| dbquery "db2" "SELECT" | join type left [dbquery "db1" "SELECT"]| fields db2_count notindb1 |
| eval db1_total=db1_count + notindb1
| eval db2_total=db2_count + notindb2
| eval confidence=tostring(round(db1_total/db2_total*100,2))."%"

this gives me a table
db1_count notindb1 db2count notinbd2 db1_total db2_total confidence
126 126 236 15 252 251 99.60%

I would like to split up the one row to look like this:


database_name count notindb total
db1 126 126 252
db2 236 15 251
confidence 99.60%

Any suggestions would be appreciated

Tags (2)
0 Karma

rsennett_splunk
Splunk Employee
Splunk Employee

I'm answering based on what you've shown us. But the question says :"Format or Splitup a row of data into three rows for reporting" however your example shows the resulting row and a single row of what you'd like it to look like...

if that's not what you meant to show, please add details to your question. (do not add a comment with detail, edit the question)

That said...

What you're producing here is a "dataset" that you can then continue to manipulate. the fact that it's coming from the db queries and the join is really irrelevant.

From your example, it looks like you want to continue your search with a pipe and merge the notindbX fields and the dbX_count fields while re-ordering the others. (you don't seem to have the database_name field so you will have to add that to the initial search)
Why would you not just merge the fields (From your example, it looks like you want to continue your search with a pipe and merge the notindbX fields and the dbX_count fields while re-ordering the others. (you don't seem to have the database_name field. Why would you not just merge the fields ([https://answers.splunk.com/answers/49394/merge-two-fields-into-one-field.html][1])
and then continue with |table ....

With Splunk... the answer is always "YES!". It just might require more regex than you're prepared for!
0 Karma

woodcock
Esteemed Legend

If you specify what field are coming out of each table (even better would be a sample row from each table) and if there is one field you would like to use to join, I will try to help. As it is, I do not understand how you are trying to merge.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...