How to use result from subsearch in my search?

[billycote]

Hi All,

My data looks like this:
sourcetype - Loginstats
contents - Hostname, host, Address
sourcetype - Clientstats
Contents host, Address, "Symbol subscriptions"

What I want to do is use a subsearch to get the results of my search to obtain Address from loginstats and then get some statistics from Clientstats.

my search is as follows:

index=contentgateway sourcetype=Loginstats "User id"="fid-idea" [search index=contentgateway sourcetype=clientstats "User id"="fid-idea"| table Address]| stats mode("Symbol subscriptions") by Address, Hostname, host

which "kinda" works. In that it gives me the Address, Hostname and host. What it doesn't give me is the mode of "Symbol subscriptions". I understand why it's not working "Symbol subscriptions" is not a part of Loginstats. I just want to figure out a way to get it to work. So is there a way to associate a mode("Symbol subscriptions") and pass it back to the main search but not have the search use that as a term for the search? Instead only search on the Address? I've tried moving the stats command inside the subsearch already...

[billycote]

this works

index=contentgateway (sourcetype=Loginstats OR sourcetype=clientstats) "User id"="fid-idea"| transaction Address
|stats avg(Symbol_subscriptions) AS syms BY Address Hostname host Action

I think the key i was missing was the transaction part. Thanks for your help. I was able to get away from the subsearch.

[woodcock]

You should not use transaction for such a simple case. You are looking for trouble.

[DalJeanis]

... I don't believe that is doing what you think it is. Even though it looks very simple, joining via transaction is making a kludge in the data.

Try woodcock's latest, which is pretty bulletproof.

...or replace | transaction Address in your working search with ...

| stats values(Symbol_subscriptions) AS syms values(hostname) AS host values(Hostname) AS Hostname BY  Address

...which is what you are currently accomplishing using transaction, and it does not have any of the hidden gotchas of the verb transaction.

Woodcock's version is slightly more accurate to the scope of the data, but they will produce identical results as long as the host-to-Address ratio is exactly 1-1.

[billycote]

Thanks for the idea. I ended up doing this. I know join isn't the greatest thing performance wise but it seems to do what I need it to

index=contentgateway sourcetype=Loginstats "User id"="fid-idea" |join Address [search index=contentgateway sourcetype=clientstats "User id"="fid-idea" |stats avg("Symbol subscriptions") AS subs by Address] |stats max(subs) by Address, Hostname, host

[woodcock]

The problem isn't performance; it is inescapable subsearch limits. Did you try my solution?

[billycote]

I did but it didn't do exactly what I wanted. It was returning a lot (too much) data by including all the values. The subsearch will only return about 200 events per day so I'm not too worried about the limitations.

[woodcock]

You can change values(*) to values(myThing1) AS myThing1 values(myThing2) AS myThing2, etc. By using a subsearch, you are creating a timebomb for somebody (maybe even your future self) later on.

[billycote]

It's funny. when I do the following:
index=contentgateway (sourcetype=Loginstats OR sourcetype=clientstats) "User id"="fid-idea" Hostname="*"| stats values(Symbol_subscriptions) AS syms BY host Address Hostname

I get no results in the syms
but
index=contentgateway (sourcetype=Loginstats OR sourcetype=clientstats) "User id"="fid-idea" Hostname="*"| stats values(Symbol_subscriptions) AS syms BY host Address
gives me results, but no Hostname, which I need.

[woodcock]

Try this:

index=contentgateway (sourcetype=Loginstats OR sourcetype=clientstats) "User id"="fid-idea" Hostname="*"| stats values(Symbol_subscriptions) AS syms values(Hostname) AS Hostname BY host Address

Also, transaction is another time-bomb of looking-for-trouble.

[woodcock]

Try this:

index=contentgateway (sourcetype=Loginstats OR sourcetype=clientstats) "User id"="fid-idea" | stats values(*) AS * BY host Address