I have two sourcetypes "clients" and "potentialclients" and each sourcetype contains address information. I want to focus marketing in areas where I have the most existing clients, so I would like to run a sub-=search against the "clients" sourcetype to identify the top 20 zip codes of of the existing clients, then use those zip codes as input to search and identify the the addresses in the "potentialclients" sourcetype matching those zip codes.
This is what I've got so far, but it's not working like I thought it would. The zip code for both source types is "Property Zip"
sourcetype="potential_clients" [search sourcetype=*clients* | top limit=20 "Property Zip" | fields + "Property Zip" | rename "Property Zip" as search ]
Not sure you need a subsearch. This may give you what you're looking for
(sourcetype="potential_clients" OR sourcetype="clients") | top limit=20 "Property Zip" by sourcetype
This will not work because say for example I had 1000 addresses for the zip code 11111 in the potential clients sourcetype and no actual clients in that zip code but had 20 clients in zip code 22222 but only an additional 300 addresses for potential clients in zip code 22222.
In this case the search would place the zip where i have no clients higher in the list.
I really do need to identify the top zips for existing clients and then use that as input to search the potential clients sourcetype.
sourcetype="potential_clients" [search sourcetype="clients" | top limit=20 "Property Zip" | return "Property Zip"]
Ok I figured it out.... the format command is what I needed.
This search does exactly what I need.
sourcetype=potential_clients [search sourcetype=clients | top "Property Zip" limit=25 | table "Property Zip" | format]