Two dbqueries searches and return difference on same row as Total

[txc168]

Trying to connect two queries returning counts from two different searches. I would like to return as one row where it takes count of
records_cycling and the count of records

This is what I was trying but when added the eval it only returned records_cycling value and nothing in other columns

| dbxquery connection="searchDB" query="(SELECT count(1) AS records_cycling from get_work....."
    |append 
    [| dbxquery connection="searchDB" query="select count(*) as records from validation "]
    | eval Difference = records - records_cycling
    | table records records_cycling difference

Ideal results to return

 records | records_cycling | difference
        5             2            3
        4             2            2

[richgalloway]

The problem comes from the append command creating a separate set of results. This set will have a records field, but not a records_cycling field; the first set of results will have a records_cycling field and no records field. That makes it impossible to do the eval.

If each query returns a single row then you can replace append with appendcols and the rest of the query should work. If each query produces the same number of events and in the same order (that is, each row in one query is related to the corresponding row in the other query) then you can also use appendcols.

Otherwise, you'll need some field that connects results of the first query to the matching result of the second query. Then you'd use stats to combine the two sets before computing the difference.

[txc168]

Ok. Yeah i had tried the appendcols but it is giving me an error.

Error in 'appendcols' command: You can only use appendcols after a reporting command (such as stats, chart, or timechart).

Am I missing some type of eval?

 | dbxquery connection="searchDB" query="(SELECT count(1) AS records_cycling from get_work....."
     |appendcols 
     [| dbxquery connection="searchDB" query="select count(*) as records from validation "]
     | eval Difference = records - records_cycling | table records record_cycling Difference

Above gives error and then if i use appendpipe its returns below, which is close but no values populated for records_cycling and difference. I guess i'm misunderstanding what each type of append is doing.

   |records | records_cycling | Difference
   1.
   2.  3454534

[richgalloway]

So appendcols is not going to work for you. That means you need to find some field that connects the results of the two queries.

[txc168]

Even if i'm only returning a count for each query? No type of join or union? Or I have to join on a ? Sorry new to the splunk world..

[richgalloway]

Try this:

| dbxquery connection="searchDB" query="(SELECT count(1) AS records_cycling from get_work....."
| stats values(records_cycling) as record_cycling
      |appendcols 
      [| dbxquery connection="searchDB" query="select count(*) as records from validation "
       | stats values(records) as records]
| eval Difference = records - records_cycling | table records record_cycling Difference

[txc168]

I'll keep playing around with it. It returned the columns correctly but is only populating the records column. which is weird since its the 2nd query. Really appreciate the assistance so far.