Query for subtracting the timechart span results of two queries

[shaycoshay]

Hello! I'm new to splunk so any help is much appreciated. I have two queries of different index. 

Query1: index=rdc sourcetype=sellers-marketplace-api-prod custom_data | search "custom_data.result.id"="*" | dedup custom_data.result.id | timechart span=1h count

 

Query2: index=leads host="pa*" seller_summary | spath input="Data" | search "0.lead.form.page_name"="seller_summary" | dedup 0.id | timechart span=1h count

I would like to write a query that executes Query1-Query2 for the counts in each hour. It should be in the same format. Thank you!!

[dtburrows3 · Accepted solution]

Using a multisearch command may be useful here to help standardize some fields before piping the two datasets into a timechart command.

Something like this I think should work.

| multisearch
    [
        | search index=rdc sourcetype=sellers-marketplace-api-prod "custom_data.result.id"="*"
            | fields + _time, index, "custom_data.result.id"
        ]
    [
        | search index=leads host="pa*" seller_summary
            | spath input="Data" 
            | search "0.lead.form.page_name"="seller_summary"
            | fields + _time, index, "0.id"
        ]
    | eval
        identifier=coalesce('custom_data.result.id', '0.id')
    | dedup index, identifier
    | timechart span=1h
        count as count
            by index
    | eval
        diff='leads'-'rdc'

 
Tried to replicate on my local instance with some similarly structured datasets and think it works out.
Resulting dataset should look something like this. (But with your indexes of course)

[richgalloway]

It may not be the most efficient method, but this should get you started.

index=rdc sourcetype=sellers-marketplace-api-prod custom_data 
| search "custom_data.result.id"="*" 
| dedup custom_data.result.id | timechart span=1h count as count1
| append [ search index=leads host="pa*" seller_summary 
  | spath input="Data" 
  | search "0.lead.form.page_name"="seller_summary" 
  | dedup 0.id 
  | timechart span=1h count as count2
| stats values(*) as * by _time
| eval diff = count1 - count2