Splunk Search

One Value from one field should compare every other value from other field and return the 3rd field value.

nomadichunters
Explorer

Query:

index=data_core sourcetype=data_log is_scheduled=1 
| rex max_match=0 field=search "savedsearch\s{0,}\"{1}(?(\w+){0,})" 
| eval anotherSavedSearchUseInSearch = if(isnull(anotherSavedSearchUseInSearch),"anotherSavedSearchNotUsedinSearch",anotherSavedSearchUseInSearch) 
| eval type1 = if((anotherSavedSearchUseInSearch != "anotherSavedSearchNotUsedinSearch"),"Title2",null()) 
| stats values(anotherSavedSearchUseInSearch) as Title2 values(type1) as type1 by title 
| sort title 
| join type=outer title 
    [ search index=data_core sourcetype=data_log is_scheduled=1 ] 
| rename title as Title1 
| rex max_match=0 field=search "index\s{0,}=\s{0,}\"{0,}\${0,}(?\*{0,}\w*\*{0,})" 
| eval indexusedinquery = if(isnull(indexusedinquery),0,1) 
| eval type2 = "Title1" 
| dedup Title1 Title2 
| eval type = type1.",".type2 
| where Title1 = "SB_EnrollmentInfo" OR Title1 = "RIO_RTL_SB_MDM_ENROLLMENT_INFO" OR Title1 = "Events_recevied_for_missing_index"  OR Title1 = "ASTMS_CORP_SB_MDM_DEVICE_INFO" 
| fields Title1 Title2 indexusedinquery type

Current Data Looks Like :

Title1                                Title2                                indexusedinquery     type
Events_recevied_for_missing_index     anotherSavedSearchNotUsedinSearch     0                    null
RIO_RTL_SB_MDM_ENROLLMENT_INFO        SB_EnrollmentInfo                     0                    Title2,Title1
SB_EnrollmentInfo                     anotherSavedSearchNotUsedinSearch     1                    null
ASTMS_CORP_SB_MDM_DEVICE_INFO         SB_EnrollmentInfo                     1                    Title2,Title1

Now my requirement is for each of value in field Title2 , should check for every other value in field Title1 until the first match of value is found.
e.g. : Second Row Value in Title2 ( i.e . SB_EnrollmentInfo) should check for the existence of the same value in Title1 , i.e. it should iterate up to row 3 , and stop there since the value matched and return me the indexusedinquery field value as 1 instead of 0 and the Total value should be summed up in indexusedinquery field (0 + 1) displayed as1`.

output should look like :

Title1                                Title2                                indexusedinquery
Events_recevied_for_missing_index     anotherSavedSearchNotUsedinSearch     0
RIO_RTL_SB_MDM_ENROLLMENT_INFO        SB_EnrollmentInfo                     1
SB_EnrollmentInfo                     anotherSavedSearchNotUsedinSearch     1
ASTMS_CORP_SB_MDM_DEVICE_INFO         SB_EnrollmentInfo                     2
Tags (1)
0 Karma

woodcock
Esteemed Legend

I did an extensive reformatting of your question and now here is your solution:

| makeresults 
| eval raw="Events_recevied_for_missing_index anotherSavedSearchNotUsedinSearch 0 null:::RIO_RTL_SB_MDM_ENROLLMENT_INFO SB_EnrollmentInfo 0 Title2,Title1:::SB_EnrollmentInfo anotherSavedSearchNotUsedinSearch 1 null:::ASTMS_CORP_SB_MDM_DEVICE_INFO SB_EnrollmentInfo 1 Title2,Title1"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<Title1>\S+)\s+(?<Title2>\S+)\s+(?<indexusedinquery>\S+)\s+(?<type>\S+)$"
| fields - _*

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

| streamstats values(Title1) AS Title1_values_temp
| reverse
| streamstats current=f window=1 last(Title1_values_temp) AS Title1_values
| fields - Title1_values_temp
| reverse
| filldown Title1_values
| eval indexusedinquery=if((mvcount(Title1_values) == mvcount(mvdedup(mvappend(Title1_values, Title2)))), "1", "0")
| streamstats sum(indexusedinquery) AS indexusedinquery
| table Title1 Title2 indexusedinquery
0 Karma

nomadichunters
Explorer

The value of Title2 will not always be present in before or next row in Title 1 , it can be in any row. Only the existance of Title2 in Title1 should be validated and if it is true then the respective indexusedinquery value should be returned and then the value should be summed.

0 Karma

woodcock
Esteemed Legend

I made it work EXACTLY as your example data and mocked output requires. If you have other requirements, then update your question to show the additional detail in the Current data looks like and output should look like sections and I will have another shot.

0 Karma

DalJeanis
Legend

@nomadichunters - 1) Please mark your code and correct the places where the interface has removed data that it thought was html. For instance, inside the first rex it is missing the <fieldname>.

2) Please describe what you are trying to achieve with the code. Do NOT describe how you are trying to do it, just describe what you are trying to achieve. There would be a much easier way, if we knew the exact use case.

For instance, the subsearch is going against the same data as the main search, and we're guessing that you are using it to return data that you had just thrown away in the stats command. So the right way to do that is to redesign the stats command.

So, start with a plain English description of the use case... "I am trying to find all of the searches that are depending on other searches, so that we can..."

0 Karma

nomadichunters
Explorer

Seriously wondering if anyone could help me out here !!!

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...