Search code examples
splunksplunk-query

Dashboard table: get count of subquery

I've been struggling with this for a few hours.

I am trying to build a simple table that will have the following:

  1. Result of a search containing an id and a date
  2. Count of a subquery using the id of the search in 1

I have two working queries that achieve what I want individually, but when I try to use a join I lose the data of the query for #2.

Query #1: index= source="" "" AND parentId=1574 | fields childId, date -> returns 333, Query #2: index= source="" "" AND childId=333 | chart count as childCount | fields childCount -> returns the count, 4 for example

When I try to join the two using something like this, I lose the count:

index=<redacted> source="<redacted" "<some query text>" AND 
    parentId=1574 
| fields childId, date
| join type=left childId [ 
    search index=<redacted> source="<redacted>" "<some query text>" AND 
    childId=333 | chart count as childCount | fields childCount
]
| table artifactId, childCount, date

I've also tried an outer join, append, etc. to no avail. The count could be 0 as well.

Any help would be appreciated,

Thanks!

Solution

  • The data from the second search is lost because the search does not return the 'childId' field expected by the join. It returns only the 'childCount' field specified in the fields command. Try fields childId childCount.