topic Re: SubSearch in Splunk Search

SubSearch

giorgioanastasi — Wed, 08 Dec 2021 15:18:36 GMT

Hi, I would have this need, that is to carry out a search that extracts all users who use iphone with SO = 9. * and then through the extracted users, search through them who has also used another device.

One solution would be to run the first search, get the list of all users and then do a new search with UsersId in input.

First search: search model = iphone so = 9. * | table UserId

Second search: search UserId IN (user list of the first search) model! = iphone

Would it be possible to do this extraction with just one search?

Thanks

Re: SubSearch

swong_splunk — Wed, 08 Dec 2021 17:02:12 GMT

Give this a try:

index=main model! = iphone

[search index=main model = iphone so = 9. *

| table UserId
| dedup UserId
| table UserId
| format]

Re: SubSearch

gcusello — Wed, 08 Dec 2021 21:52:24 GMT

Hi @giorgioanastasi,

you could try something like this:

index=main | stats count(eval(model="iphone" AND so="9.*")) AS iphone9 count(eval(NOT (model="iphone" AND so="9.*"))) AS not_iphone9 BY UserId

Ciao.

Giuseppe

Re: SubSearch

giorgioanastasi — Thu, 09 Dec 2021 11:27:35 GMT

Hi Giuseppe !! How are you ??

you should know these files very well! 😉

unfortunately with your search it does not work, strangely:count(eval(SO="ios" AND VersSO="9.*")) AS iphone9 is always 0

this is the search:

tag=Operativo | stats count(eval(SO="ios" AND VersSO="9.*")) AS iphone9 count(eval(NOT (SO="ios" AND VersSO="9.*"))) AS not_iphone9 BY NDG

Re: SubSearch

PickleRick — Thu, 09 Dec 2021 22:02:17 GMT

I can think of several more or less ugly solutions to this problem 😉

But an elegant one is with eventstats

index=whatever
| eventstats dc(model) as modelcount by UserId
| search model=iphone so=9.* modelcount>1

That will hovewer only give you the iphone of users who use iphone 9.* as well as other phone. If that's enough because you only want the users themselves, that's ok.

If you also want to know what other phones they use, you might use similar trick to that proposed by @gcusello

index=whatever
| eventstats dc(model) as modelcount count(eval(model="iphone" AND match(os,"9\..*")) as iphoner by UserId
| search iphoner>0 modelcount>1
| sort UserId

Re: SubSearch

gcusello — Fri, 10 Dec 2021 07:17:15 GMT

Hi @giorgioanastasi,

Ciao Giorgio, it's a pleasure to speak with you!

please try something like this (that's similar to the solution of @PickleRick.:

index=whatever | eval iphone9=if(model="iphone" AND match(os,"9\..*"),"Yes","No"), | stats count(eval(iphone9="Yes") as iphoner dc(model) as modelcount by UserId | search iphoner>0 | sort UserId

Ciao.

Giuseppe

Re: SubSearch

giorgioanastasi — Fri, 10 Dec 2021 09:52:35 GMT

So, of the suggested searches, only this one (swong_splunk) extracts the results (correct?):

This search extracts all customer codes that use ios 9. * but have also used a different device. I would need the opposite, that is, only customers who have used exclusively ios 9. * (but I can get them by difference from the list of those who use ios 9. * minus the search list above)

This research does not extract anything:

tag=Operativo
| eval iphone9=if(SO=ios AND match(VersSO,"9\..*"),"Yes","No")
| stats count(eval(iphone9="Yes")) as iphoner dc(model) as modelcount by IdCliente
| search iphoner>0
| sort IdCliente

Re: SubSearch

giorgioanastasi — Fri, 10 Dec 2021 18:10:50 GMT

So, of the suggested searches, only this one (swong_splunk) extracts the results (correct?):

This research does not extract anything:

tag=Operativo
| eval iphone9=if(SO=ios AND match(VersSO,"9\..*"),"Yes","No")
| stats count(eval(iphone9="Yes")) as iphoner dc(model) as modelcount by IdCliente
| search iphoner>0
| sort IdCliente