Converting subsearch to a search with count and distinct count

[cpeteman]

So I have two searched joined together that works great, Unfortunately the subsearch reaches the time limit even on fast mode especially since I need to run the search over 3 years. I'd like a way to get rid of the subsearch while keeping the end results the same.

search_term1 nid="*" 
| dedup nid | search NOT hostname="*" 
| rex field=nid "(?<ip>.*)@(?<network>.*)" 
| stats count AS nid_count by network 
| join [search search_term1 nid="*" NOT hostname="*"
    | rex field=nid "(?<ip>.*)@(?<network>.*)"
    | stats count AS event_count by network]

Let me know if you have questions or need any more information.

[okrabbe_splunk]

Maybe I am reading this wrong but you are basically looking to get a distinct count on the nid field and also combine that with an overall event count by network?

search search_term1 nid="*" NOT hostname="*"
| rex field=nid "(?<ip>.*)@(?<network>.*)"
| stats dc(nid) as nid_count count AS event_count by network

[okrabbe_splunk]

ok! I see lguinn already answered you question.

[cpeteman]

Yes. That's pretty much what I was doing.

[lguinn2 · Accepted solution]

Try this

search_term1 nid="*" NOT hostname="*"
| rex field=nid "(?<ip>.*)@(?<network>.*)"
| stats count AS event_count distinct_count(nid) as nid_count by network

It should run much faster, as well as avoiding the subsearch limits.

[cpeteman]

That works great! I switched the stats commands at the end to get the fields in the same order but otherwise perfect.