topic Re: Nested search? in Splunk Search

Nested search?

dbcase — Thu, 14 Dec 2017 23:09:16 GMT

Hi,

I have 3 queries that I need to combine.

The first one gives a list of clientSessionId's

index=wholesale_app DynamicChoice Properties.index=3 buildTarget=* product=* UNSUPPORTED_FORMAT|table clientSessionId

The next one would use the list from the first query to display a list of account Ids (I've already combined the first query with the second query)

index=wholesale_app CustomAnalytic Properties.index=1 [search index=wholesale_app DynamicChoice Properties.index=3 buildTarget=* product=* UNSUPPORTED_FORMAT|table clientSessionId]|rename Properties.args{} as properties  |rex  "accountId.....(?<accountid>\w+)"|stats count by accountid|where len(accountid)<10

This is the part that I don't know how to do... On the third query I need to extract the URL. The basic query is this

index=wholesale_app DynamicChoice Properties.index=0|rename Properties.args{} as properties |stats count by properties

The common linking field across all the queries is clientSessionId. I can get two queries to work but not sure how to get the third one merged in. I thought it would be something like this but it barfs

index=wholesale_app DynamicChoice Properties.index=0 [search index=wholesale_app CustomAnalytic Properties.index=1 |rex  "accountId.....(?<accountid>\w+)"|where len(accountid)<10|stats count by accountid [search index=wholesale_app DynamicChoice Properties.index=3 buildTarget=* product=* UNSUPPORTED_FORMAT|stats count by clientSessionId]]|rename Properties.args{} as properties |stats count by accountid properties

Any thoughts???

Re: Nested search?

micahkemp — Fri, 15 Dec 2017 00:08:34 GMT

Edited to show the search that led to your final search:

Edited again cause, oops, I forgot to change my base search back to your base search.

 index=wholesale_app 
     [ search index=wholesale_app DynamicChoice Properties.index=3 buildTarget=* product=* UNSUPPORTED_FORMAT 
     | table clientSessionId] 
 | rename Properties.args{} as properties 
 | rex "accountId.....(?<accountid>\w+)"
 | eval url=if('Properties.index'="0", properties, NULL)
 | stats list(accountid) AS accountid, list(url) AS url BY clientSessionId

Re: Nested search?

dbcase — Fri, 15 Dec 2017 00:52:39 GMT

Hi Michakemp,

Apologies, I should have included data examples. I tried your query with high hopes but no joy... Not sure how you even got that far without data examples. Impressive!

On to the data examples

Here is an event for the first query (unsupported_format)

{   [-] 
     Properties:    {   [-] 
         args:  [   [-] 
         UNSUPPORTED_FORMAT 
        ]   
         category:   Event  
         index:  3  
    }   
     analyticType:   DynamicChoice  
     buildTarget:    blah   
     clientSessionId:    DXJNRLI-AIUGREA    
     product:    blah   
}

Here is one from the second query

{   [-] 
     Properties:    {   [-] 
         args:  [   [-] 
         {"accountId":"exr413508","customerId":"1002330"}   
        ]   
         category:   Event  
         index:  1  
    }   
     analyticType:   CustomAnalytic 
     buildTarget:    blah   
     clientSessionId:    DXJNRLI-AIUGREA    
     product:    blah   
}

And finally the third query

{   [-] 
     Properties:    {   [-] 
         args:  [   [-] 
         https://beta.something.com 
        ]   
         category:   Event  
         index:  0  
    }   
     analyticType:   DynamicChoice  
     buildTarget:    blah   
     clientSessionId:    DXJNRLI-AIUGREA    
}

I hope that helps because I'm stumped

Re: Nested search?

dbcase — Fri, 15 Dec 2017 00:59:30 GMT

I was thinking something like appendcols might help but I've really not used that one before.

Re: Nested search?

micahkemp — Fri, 15 Dec 2017 01:01:32 GMT

Can you paste the raw text instead of the syntax highlighted version? Click the 'show as raw text' link for your search result.

Re: Nested search?

dbcase — Fri, 15 Dec 2017 01:06:16 GMT

sure!

first one
{"analyticType":"DynamicChoice","buildTarget":"blah","product":"blah","clientSessionId":"DXJNRLI-AIUGREA","Properties":{"index":3,"args":["UNSUPPORTED_FORMAT"],"category":"Event"}}

second one

{"analyticType":"CustomAnalytic","buildTarget":"blah","product":"blah","clientSessionId":"DXJNRLI-AIUGREA","Properties":{"index":1,"args":["{\"accountId\":\"exr413508\",\"customerId\":\"1002330\"}"],"category":"Event"}}

third one

{"analyticType":"DynamicChoice","buildTarget":"blah","clientSessionId":"DXJNRLI-AIUGREA","Properties":{"index":0,"args":["https://beta.something.com"],"category":"Event"}}

Re: Nested search?

micahkemp — Fri, 15 Dec 2017 01:14:11 GMT

Does the output of this look like what you want:

source=*dbcase* 
    [ search source=*dbcase* DynamicChoice Properties.index=3 buildTarget=* product=* UNSUPPORTED_FORMAT 
    | table clientSessionId] 
| rename Properties.args{} as properties 
| rex "accountId.....(?<accountid>\w+)"
| eval url=if('Properties.index'="0", properties, NULL)
| stats list(accountid) AS accountid, list(url) AS url BY clientSessionId

Re: Nested search?

dbcase — Fri, 15 Dec 2017 01:30:02 GMT

whooo hoooo!

Yours was very very close! I made a couple of modifications and presto!

Here is what I ended up with

index=wholesale_app [ search index=wholesale_app DynamicChoice Properties.index=3 buildTarget=* product=* UNSUPPORTED_FORMAT 
     | table clientSessionId] 
 | rename Properties.args{} as properties 
 | rex "accountId.....(?<accountid>\w+)"
 | eval url=if('Properties.index'="0" AND 'analyticType'="DynamicChoice", properties, NULL)
 | stats list(accountid) AS accountid, values(url) AS url BY clientSessionId |where len(accountid)<10 |where len(url)>1

THank you sooooooooo much!!!