Need to run a subsearch with practically unlimited rows

[anthony_copus]

Hi,

Currently I'm trying to run a query which take the results of a subsearch as a parameter as follows:

index="video" [search index=page article_id!="" type=d | stats count by article_id | fields article_id] | stats count by platform

So the aim is to retrieve all the count from the video index which have the same article_id values which are retrieved from the subsearch. However, the subsearch should return a vast amount of results (well over 10000) so this method doesn't seem too practical.

Is there a better method of achieving the same ends or a solution to the limit which would be more than simply raising it a little?

Anthony

[DalJeanis]

You can do it without a subsearch at all. This looks a little inelegant, so it can probably be coded a bit tighter, but here's some unlimited code that ought to get you the same result as yours...

(index="video" article_id!="") or (index="page" article_id!="" type=d)
| table index article_id type platform
| fillnull value="" type platform
| stats count as itemcount, list(platform) as platform, list(type) as type by article_id index
| eval platform=if(platform="",null(),platform)
| eval type=if(type="",null(),type)
| eval itemcount=if(index="page", 0,itemcount)
| stats sum(itemcount) as itemcount, list(platform) as platform, values(type) as type by article_id
| search type=d
| stats sum(itemcount) as count, by platform

The above is based on the assumption that your video records (effectively) contain three fields - index=video, article_id=somevalue, and platform=someothervalue (no type). Likewise, your page records contain index=page, article_id=somevalue, and type=d (no platform), and that you only want records from video when the article_id is on a type d record in the page index.

[somesoni2]

Try updating following in your limits.conf. (need restart)

[subsearch] 
maxout = 50000

[format] 
maxresults = 50000

[aweitzman]

map might be of use here:

index=page article_id!="" type=d | dedup article_id | table article_id | map search="search index=video article_id=$article_id$ | stats count by platform" maxsearches=1000000

(I changed the first stats count to dedup since you don't seem to be doing anything with the count coming from the page index, so you can avoid wasting the resources computing it.)

Note: This search may take a very, very long time. Be prepared.