topic Re: Compare Results From Two Searches in Splunk Search

Compare Results From Two Searches

IRHM73 — Fri, 14 Oct 2016 11:46:13 GMT

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

Re: Compare Results From Two Searches

jkat54 — Fri, 14 Oct 2016 13:18:53 GMT

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.

Re: Compare Results From Two Searches

javiergn — Fri, 14 Oct 2016 13:32:30 GMT

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:

| 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
]

| 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:

| 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

Re: Compare Results From Two Searches

IRHM73 — Fri, 14 Oct 2016 13:46:48 GMT

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

Re: Compare Results From Two Searches

IRHM73 — Fri, 14 Oct 2016 13:48:35 GMT

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

Re: Compare Results From Two Searches

javiergn — Fri, 14 Oct 2016 14:25:06 GMT

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.

Re: Compare Results From Two Searches

javiergn — Tue, 29 Sep 2020 11:25:44 GMT

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:

Re: Compare Results From Two Searches

somesoni2 — Fri, 14 Oct 2016 14:41:32 GMT

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

Re: Compare Results From Two Searches

IRHM73 — Sat, 15 Oct 2016 14:42:51 GMT

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

Kind Regards

Chris

Re: Compare Results From Two Searches

IRHM73 — Sat, 15 Oct 2016 14:44:18 GMT

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