Compare Results From Two Searches

[IRHM73]

Hi, I wonder whether someone may be able to help me please.

I'm using the following subqueries:

The first extracts a list of macros that have been created.

| rest /servicesNS/-/-/admin/macros count=0 splunk_server=local
| where isnull(args)
| where like(definition, "%index=dg_%")
| rex field=definition "auditSource\=(?<source>[^\s]+)\)"
| rex field=title "(?<indexorig>[^\s]+)_"
| eval index="dg_" .indexorig
| fields source index

The second provides me with a list of indexes that have been created.

| eventcount summarize=false index="dg_*"
| dedup index
| fields index

What I'm trying to do is create a query which joins via the "index" fields, but produces a list of 'sources' in the first column (first subquery), and then 'indexes' in the second (second subquery.

Comparing the two, I'm expecting gaps in the index column.

I've tries using the 'append, 'join', 'appendcols' commands, but so far I've been unable to get this to work.

I just wondered whether someone could look at this please and offer some guidance on how I may achieve this.

Many thanks and kind regards

Chris

[somesoni2 · Accepted solution]

Give this a try

| rest /servicesNS/-/-/admin/macros count=0 splunk_server=local
 | where isnull(args)
 | where like(definition, "%index=dg_%")
 | rex field=definition "auditSource\=(?<source>[^\s]+)\)"
 | rex field=title "(?<indexorig>[^\s]+)_"
 | eval index="dg_" .indexorig
 | table source index
 | join type=left index [
     | eventcount summarize=false index="dg_*"
     | dedup index
     | table index | eval iCreated="Y"]
 | eval index=if(iCreated="Y",index,null())
 | table source index

[IRHM73]

Hi @somesoni2, this works brilliantly. Thank you for taking the time to reply to my post with a solution.

Kind Regards

Chris

[javiergn]

You can use join for this but remember no right join is available so you have two options depending on what you are trying to display:

1)

| rest /servicesNS/-/-/admin/macros count=0 splunk_server=local
| where isnull(args)
| where like(definition, "%index=dg_%")
| rex field=definition "auditSource\=(?<source>[^\s]+)\)"
| rex field=title "(?<indexorig>[^\s]+)_"
| eval index="dg_" .indexorig
| fields source index
| join type=left index [
    | eventcount summarize=false index="dg_*"
    | dedup index
    | fields index
]

2)

| eventcount summarize=false index="dg_*"
| dedup index
| fields index
| join type=left index [
    | rest /servicesNS/-/-/admin/macros count=0 splunk_server=local
    | where isnull(args)
    | where like(definition, "%index=dg_%")
    | rex field=definition "auditSource\=(?<source>[^\s]+)\)"
    | rex field=title "(?<indexorig>[^\s]+)_"
    | eval index="dg_" .indexorig
    | fields source index
]

Actually, three if all you care are those cases where index is present in both:

3)

| rest /servicesNS/-/-/admin/macros count=0 splunk_server=local
| where isnull(args)
| where like(definition, "%index=dg_%")
| rex field=definition "auditSource\=(?<source>[^\s]+)\)"
| rex field=title "(?<indexorig>[^\s]+)_"
| eval index="dg_" .indexorig
| fields source index
| join index [
    | eventcount summarize=false index="dg_*"
    | dedup index
    | fields index
]

If none of them work for you please post the query you are running so that we can help you debugging the problem.

Thanks,
J

[IRHM73]

Hi @javiergn, thank you for coming back to me.

My solution was the same as your solution no.2

But as I say, unfortunately this isn't working.

The end result should be:

Column 1 = content of 'rest' query which has 227 rows.
Column 2 = content if 'eventcount' query which has 38 rows

So when they are put together there should be blank rows in column 2.

I hope this helps.

Many thanks and kind regards

[javiergn]

Given what you are saying you are looking for a left join where your left table is is the rest query and not the other way around. That is, solution 1 above. Unless I am not understanding your request.

[javiergn]

This is the way I replicated your request. Look at the values of index_left and index_right if you want to understand what I'm talking about:

| rest /servicesNS/-/-/admin/macros count=0 splunk_server=local
| where isnull(args)
| where like(definition, "%index=%")
| rex field=definition "index=(?<index>\S+)"
| eval index_left = index
| eval source=title
| fields source index index_left
| join type=left index [
    | eventcount summarize=false index="*"
    | dedup index
    | eval index_right = index
    | fields index index_right
]

And this is the output:

[IRHM73]

Hi @javiergn, thank you for taking the time to come back to me with this. Please see the working solution from @somesoni2.

Kind Regards

Chris

[jkat54]

Can you share your "join" search? I'm not able to replicate exactly what you're doing but I was able to get this to work:

| rest /servicesNS/-/-/admin/macros | rex field=definition "index=(?<index>\w+)" | search index=* | fields index | join index [ | makeresults count=1 | eval index="_audit" | eval test="test" | fields index test]

It's similar in that both searches start with a data generating command. So it technically should have worked for you if you followed the correct syntax.

[IRHM73]

Hi @jkat54, thank you for taking the time to come back to me with this.

My join query is as below and indeed matches one of the solutions provided by @javiergn.

 | eventcount summarize=false index="dg_*"
 | dedup index
 | fields index
 | join type=left index [
     | rest /servicesNS/-/-/admin/macros count=0 splunk_server=local
     | where isnull(args)
     | where like(definition, "%index=dg_%")
     | rex field=definition "auditSource\=(?<source>[^\s]+)\)"
     | rex field=title "(?<indexorig>[^\s]+)_"
     | eval index="dg_" .indexorig
     | fields source index
 ]

But as I say, unfortunately this isn't working.

The end result should be:

Column 1 = content of 'rest' query which has 227 rows.
Column 2 = content if 'eventcount' query which has 38 rows

So when they are put together there should be blank rows in column 2.

Does this make sense.

Many thanks and kind regards

Chris