topic Re: How do i search non matching values from two different indexes and display in Splunk Search

How do i search non matching values from two different indexes and display

saifullakhalid — Tue, 29 Sep 2020 16:54:56 GMT

This is what I am doing

extract value until the first occurrence of char & using the search string

My log sample:

?pyActivity=FinishAssig&pzPrimaryPageName=pyWorkPage&pzTransactionId=xxxxx&pzFromFrame=pyWorkPage HTTP/1.1" 200 1383

?pyActivity=Sh-Harness&Purpose=Perform&SkipFrame=true&TaskIndex=1&HarnessMode=ACTION&TaskValue=CAIXXXr_____CA&TaskHTML=CAXXXℜadOnly=-1&FrameName=pyWorkPage&pzPrimaryPageName=pyWorkPage HTTP/1.1" 200 14547

?pyActivity=Data-Por.Get&IsBMLogin=true∈StandardsMode=false&AJAXTrackID=1&pzHarnessID=HIDXXXXHTTP/1.1" 200 10

Sample expected output for both indexes:

PA Count

FinishAssig 1
Sh-Harness 1
Data-Por.Get 1

since I have two indexes index="prod_clean" AND index="mod_clean"

Q1.How do I write the search to get non matching values from two indexes and display in a table ?
Q2. How do I write the search to get only matching values and there count in each index?

I tried Q2 but I know it is not correct it is getting the total count instead of individual ? and also suggest for Q1.

Re: How do i search non matching values from two different indexes and display

MuS — Tue, 21 Nov 2017 18:29:56 GMT

Hi there,

you should use the code formatting for SPL and regex to keep all the special characters, code formatting can be applied by selecting the text and press either CTRL-K or the 101010 button.
This helps people to be able to help because they can see all of your SPL and regex.

cheers, MuS

Re: How do i search non matching values from two different indexes and display

DEAD_BEEF — Tue, 21 Nov 2017 18:34:24 GMT

Can you be more specific in your question? it's not very clear what you are trying to accomplish. Are you trying to count the number of times you see FinishAssig, Sh-Harness, and Data-Por.Get ? I am not sure what you are getting by using coalesce on the same field (PA & PA). That is not what is intended for coalesce. Can you explain your question a bit more?

(index="prod_clean" OR index="mod_clean")
| rex field=_raw "=(?<CAPTURE>[^&]+)"
| eval matchID=coalesce(PA,PA)
| stats count by index CAPTURE matchID

Re: How do i search non matching values from two different indexes and display

somesoni2 — Tue, 29 Sep 2020 16:55:02 GMT

Q1.How do I write the search to get non matching values from two indexes and display in a table ?

index="prod_clean" OR index="mod_clean" | rex field=_raw "\?pyActivity=(?<PA>([^&]+))" 
| stats count AS "Count" dc(index) as reportedIn values(index) as Index by PA 
| where reportedIn=1 
| table PA Count Index

Update If you only looking for PA's present in prod_clean but not in mod_clean, try this

index="prod_clean" OR index="mod_clean" | rex field=_raw "\?pyActivity=(?<PA>([^&]+))" 
| stats count AS "Count" dc(index) as reportedIn values(index) as Index by PA 
| where reportedIn=1 AND Index="prod_clean"
| table PA Count Index

Q2. How do I write the search to get only matching values and there count in each index?
Fixed typo

index="prod_clean" OR index="mod_clean" | rex field=_raw "\?pyActivity=(?<PA>([^&]+))" 
| stats count AS "Count" by PA index
| eventstats dc(index) as reportedIn by PA 
| where reportedIn=2
| chart sum(Count) as Count over PA by index

Re: How do i search non matching values from two different indexes and display

DEAD_BEEF — Tue, 21 Nov 2017 18:43:55 GMT

Your solutions are always so clean.

Re: How do i search non matching values from two different indexes and display

saifullakhalid — Tue, 29 Sep 2020 16:55:05 GMT

I have 2 access logs of same format inserted into 2 different indexes ( index="prod_clean" AND index="mod_clean" )

I need to pull URL and there execution count .
From the log sample below I need to extract URL starting from ?proactivity=
For which I am using the search query as below.

index="prod_clean" | rex field=_raw "\?pyActivity=(?<PA>([^&]+))" | stats count AS "Count" by PA | table "PA" "Count"

index="mod_clean" | rex field=_raw "\?pyActivity=(?([^&]+))" | stats count AS "Count" by PA | table "PA" "Count"

The above is done separately , so I wanted to write a single query to get matching URLs from both indexes and there individual execution counts.

Also same for non matching which is present in index prod_clean but not present in 'mod_clean' and its count.

My full log sample is here :

1x.xx.xxx.xxx - - 11xxxxx4 [03/Oct/2017:08:01:54 -0400] - /pxxx/Gxxxxt/uxxxxxxxxx4[/!TABTHREAD1 HTTP/1.1 oxxx-xxx.xxx.net TIME:0/123717 "POST /pxxxb/Gxxxxt/uxxxxxxxxxxxxxxxxx4%5B/!TABTHREAD1?prActivity=Cxxxxxxxxx-xxxx.xxxxxx%20ℜquest_Type=&xxxxxTYPE_CD=COUNTRY&Exxxxxxxx_CD=⟪uageCode=&CountryCode=&PRODUCT_LINE_CD=®ION_CD=&LOB=&LOB_SUB_CD=&Count= HTTP/1.1" 200 4011


1x.xx.xxx.xxx - - - [03/Oct/2017:08:01:54 -0400] - /pddddb/Gdddd/xxxxxxxxxxxxxxxxxx[/themeimages/h1expand_theme_ccddd.gif!!.gif HTTP/1.1 oxxxxxxxxxxx.aig.net TIME:0/12758 "GET / /pddddb/Gdddd/xxxxxxxxxxxxxxxxxx[/themeimages/h1expand_theme_ccddd.gif!!.gif HTTP/1.1" 200 69

1x.xx.xxx.xxx- - 1ssssss4 [03/Oct/2017:08:02:09 -0400] - /pxxxx/Gxxxxxt/uxxxxxxxxxxxxxxxxx4[/!TABTHREAD1 HTTP/1.1 oxxx-xxx.xx.net TIME:0/117091 "POST /pxxxb/Gxxxt/xxxxxxxxxxxxxxxxxxxxB/!TABTHREAD1?prActivity=ReloadSection&pzIxxxd=xxxxxxxxxxxxxxxxxxx&pzFromFrame=pyxxxx&pzxxxxxxxxxxxe=pyxxxxxxxxe&pzxxxxxxx=false&StreamName=AddPropertyDetails&BaseReference=xxxxxxxxxx.xxxxxxxxxxe.Prxxxxxxx&Stxxxxxxxxxxxss=xxxxxxx-Section&bClientValidation=true&FieldError=ERRORTEXT⪻eActivity=&xxxxxxxxxge=true&HexxxxxxxxnName=SubxxxxxxorkObjectHeaderB∈StandardsMode=true&AJAXTrackID=5&pzHarnessID=HIDxxxxxxxxx HTTP/1.1" 200 4512

Re: How do i search non matching values from two different indexes and display

saifullakhalid — Tue, 21 Nov 2017 18:53:58 GMT

I have started using CTRL+K now. thanks

Re: How do i search non matching values from two different indexes and display

saifullakhalid — Tue, 29 Sep 2020 16:55:08 GMT

Q1: This should get values which is present in index prod_clean but not present in 'mod_clean' and its count.
When I run the above Q1 search I get both index values and there count.

Q2: This search gives No results found. but I see some matching records when search manually in both indexes.

Re: How do i search non matching values from two different indexes and display

DEAD_BEEF — Tue, 21 Nov 2017 19:37:15 GMT

I see now. You want two queries:

Query 1 will give you a table of statistics showing the index, url (PA) when PA was present in mod index BUT NOT in index prod and vice versa (present in prod BUT NOT index mod)
Query 2 will give you the a table of statistics formatted as index, url (PA) when the PA is present in BOTH indexes (mod & prod)

Do I understand correctly now?

Re: How do i search non matching values from two different indexes and display

somesoni2 — Tue, 21 Nov 2017 19:47:18 GMT

There was an typo on the 2nd query, fixed, should work now.

Provided an updated query for Q1 for updated requirement. Check that as well.

Re: How do i search non matching values from two different indexes and display

saifullakhalid — Tue, 21 Nov 2017 20:25:02 GMT

It works perfectly thanks
But I think my rex has a issue though.

Though all the line in code has char & , but there are few line like below and due to this it consider entire line starting from pyActivity= as unique. so even there is match in mod it consider as unique.

?pyActivity=CXXXXXs-FW-XXXXXX HTTP/1.1" 200 549

Re: How do i search non matching values from two different indexes and display

DEAD_BEEF — Tue, 21 Nov 2017 21:13:43 GMT

I see. Change your regex so that it MUST match between the = and the & sign.

| rex field=_raw "\?pyActivity=((?<PA>[^&].*?)(?=&))"

Re: How do i search non matching values from two different indexes and display

somesoni2 — Tue, 21 Nov 2017 21:15:41 GMT

Try this for your regex

| rex field=_raw "\?pyActivity=(?<PA>[^&\s\"]+)"