Splunk Search

Compare result of three different index searches

runiyal
Path Finder

I have 3 Indexers I have data. Two Indexers are the source and Third one is the target. So if I am I am tryinng to Adding any file to source, it should ultimately added to target too.

Source Index 1 - Search

index="1" <search creteria> | spath field=name | table name

Source Index 1 - Result

abc.pdf
xyz.pdf
klm.pdf

Source Index 2 - Search

index="2" <search creteria? | rex field=_raw "(?P<name>[^\\\]+)$" | table name

Source Index 2 - Result

123.pdf
456.pdf

Target Index 3 - Search

index="3"  | rex field=_raw "Converted file Name  (?.*)" | table name

Target Index 3 - Result

abc.pdf
xyz.pdf
123.pdf
456.pdf
789.pdf

Need to report -
1. If all the files added in Source 1 & 2 are there in Target 3
2. If there is any difference then generate a report
3. Also mention about the files which are in Target but never came from Source 1 & 2
4. Result should have File Name as well as the source index where it resides (although name should be custom/user-friendly)

So, from our example, we should have a report like -

FileName        Index   
klm.pdf     SrcInx1 (instead of 1)
789.pdf     TgtInx3 (instead of 3)

Your inputs will be appreciated. Thanks!

0 Karma
1 Solution

woodcock
Esteemed Legend

Try this:

(index="1" <search creteria>) OR (index="2" <search creteria>) OR index="3"
| spath field=name
| rex field=_raw "(?<name_from2>[^\\\]+)$"
| rex field=_raw "Converted file Name  (?<name_from3>.*)"
| eval name = coalesce(name, name_from2, name_from3)
| stats dc(index) AS index_count values(index) AS index BY name
| search (NOT index="3") OR NOT (index="1" OR index="2")
| replace 1 WITH SrcInx1, 2 WITH SrcInx2, 3 WITH TgtInx3 IN index

Run-anywhere Poc:

| makeresults 
| eval raw="index=1,name=abc.pdf index=1,name=xyz.pdf index=1,name=klm.pdf index=2,name=123.pdf index=2,name=456.pdf index=3,name=abc.pdf index=3,name=xyz.pdf index=3,name=123.pdf index=3,name=456.pdf index=3,name=789.pdf" 
| makemv raw 
| mvexpand raw 
| rename raw AS _raw 
| kv 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| stats dc(index) AS index_count values(index) AS index BY name 
| search (NOT index="3") OR NOT (index="1" OR index="2")
| replace 1 WITH SrcInx1, 2 WITH SrcInx2, 3 WITH TgtInx3 IN index

View solution in original post

0 Karma

woodcock
Esteemed Legend

Try this:

(index="1" <search creteria>) OR (index="2" <search creteria>) OR index="3"
| spath field=name
| rex field=_raw "(?<name_from2>[^\\\]+)$"
| rex field=_raw "Converted file Name  (?<name_from3>.*)"
| eval name = coalesce(name, name_from2, name_from3)
| stats dc(index) AS index_count values(index) AS index BY name
| search (NOT index="3") OR NOT (index="1" OR index="2")
| replace 1 WITH SrcInx1, 2 WITH SrcInx2, 3 WITH TgtInx3 IN index

Run-anywhere Poc:

| makeresults 
| eval raw="index=1,name=abc.pdf index=1,name=xyz.pdf index=1,name=klm.pdf index=2,name=123.pdf index=2,name=456.pdf index=3,name=abc.pdf index=3,name=xyz.pdf index=3,name=123.pdf index=3,name=456.pdf index=3,name=789.pdf" 
| makemv raw 
| mvexpand raw 
| rename raw AS _raw 
| kv 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| stats dc(index) AS index_count values(index) AS index BY name 
| search (NOT index="3") OR NOT (index="1" OR index="2")
| replace 1 WITH SrcInx1, 2 WITH SrcInx2, 3 WITH TgtInx3 IN index
0 Karma

runiyal
Path Finder

Hello Woodcock,

I am getting this error while running this-
Error in 'where' command: The 'not' function is unsupported or undefined.
The search job has failed due to an error. You may be able view the job in the Job Inspector.

0 Karma

woodcock
Esteemed Legend

Quite correct; just swap where for search.

0 Karma

runiyal
Path Finder

I am getting result as raw as following -

klm.pdf ABC-07152019194038128-85238-10001.pdf ABC Firm001-00-2122019-07-15 T19:40:39.859-04:00 ABC-07152019194038128-85238-10001 Web00138283WebsiteCC ABCUnrestricted........................

I want result like just -

 FileName        Index    
 klm.pdf        SrcInx1    (instead of 1)
 789.pdf        TgtInx3    (instead of 3)
0 Karma

woodcock
Esteemed Legend

See updated answer.

0 Karma

runiyal
Path Finder

Hello Woodcock, This is really cool. Only issue is I have to add in the | eval raw= **<>**
so that all the filename and Index can extracted from these three searches and presented to solution piece for line 10/11/12.

0 Karma

woodcock
Esteemed Legend

I do not understand what you mean but in any case, you have 99% of the answer and should be able to walk the last 1% across the finish line on your own, right?

0 Karma

runiyal
Path Finder

Thanks woodcock for your help. I will for sure...

0 Karma

woodcock
Esteemed Legend

Be sure to come back here and click Accept when you do.

0 Karma

runiyal
Path Finder

It worked but I am getting the whole raw like -

first-07152019194038128-85238-10001.pdfABC-07152019194038128-85238-10001.pdfABC Firm001-00-2122019-07-15T19:40:39.859-04:00ABC-07152019194038128-85238-10001Web00138283WebsiteCCAOBUnrestrictedAnnetteNovelaquestInterceptor:42] Status code : {}200 19:40:41,087 INFO [app-exec-1032][LoggingRequestInterceptor:43] Status text : {}OK 19:40:41,087 INFO [app-exec-1032][LoggingRequestInterceptor:44] =======================ABC Rest Service end================================================= 19:40:41,110 INFO [app-exec-1032][AbcController:125] The HTTP Status200 19:40:41,110 INFO [app-exec-1032][AbcController:293] Rest Service call is Successfull and started the email.first-07152019194038128-85238-10001 19:40:42,753 INFO [app-exec-1032][AbcController:298] Mail Sent to test@aol.com

I just want the file name like -
first-07152019194038128-85238-10001.pdf

I need to see the resultset in this format -

 FileName        Index    
 klm.pdf        SrcInx1    (instead of 1)
 789.pdf        TgtInx3    (instead of 3)
0 Karma
Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...