Splunk Search

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

saifullakhalid
Explorer

This is what I am doing

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

index="prod_clean" | rex field=_raw "\?pyActivity=(?([^&]+))" | 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"

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.

index="prod_clean" OR index="mod_clean" | rex field=_raw "PreActivity=(?([^&]+))" | eval matchID=coalesce(PA,PA)
| stats values(*) as * by matchID | stats count AS "Count" by matchID | table "matchID" "Count"

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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

saifullakhalid
Explorer

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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try this for your regex

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

DEAD_BEEF
Builder

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

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

saifullakhalid
Explorer

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

DEAD_BEEF
Builder

Your solutions are always so clean.

0 Karma

DEAD_BEEF
Builder

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
0 Karma

saifullakhalid
Explorer

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
0 Karma

DEAD_BEEF
Builder

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?

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

saifullakhalid
Explorer

I have started using CTRL+K now. thanks

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...