I'm trying to optimize this report to successfully run without errors. It will currently run for 3-5 hours and grow to 750+MB then fails, then starts again and runs for 15 hours and grow to 8MB and complete. After it completes, there are no results in the report.
This is the query running over last 30 days.
index=web OR index=web_long sourcetype="*iis*" NOT ("HealthCheck" OR "localhost" OR "F5" OR "*LtmActivityMonitor*") NOT cs_uri_stem="/"
| eval id=coalesce(upper(app_id), upper(id))
| rename id AS AppId
| join type=inner AppId, host overwrite=false
[ search index=im sourcetype=db LogType="web" earliest=-45d
| eval host=lower(ServerName) ]
| head 500000
| eval missing_field=mvappend(
case(isnull(c_ip), "c_ip"),
case(isnull(cs_bytes), "cs_bytes"),
case(isnull(cs_host), "cs_host"),
case(isnull(cs_method), "cs_method"),
case(isnull(cs_uri_stem), "cs_uri_stem"),
case(isnull(cs_version), "cs_version"),
case(isnull(date), "date"),
case(isnull(s_computername), "s_computername"),
case(isnull(s_ip), "s_ip"),
case(isnull(s_port), "s_port"),
case(isnull(sc_bytes), "sc_bytes"),
case(isnull(sc_status), "sc_status"),
case(isnull(cs_Referer), "cs_Referer"),
case(isnull(cs_User_Agent), "cs_User_Agent"),
case(isnull(time), "time"),
case(isnull(x_forwarded_for), "x_forwarded_for")
)
| fillnull value=None
| mvexpand missing_field
| where missing_field != ""
| stats
latest(_raw) AS latest_raw
BY AppId, index, sourcetype, missing_field, host, source
| stats
values(missing_field) AS missing_field
BY AppId, index, sourcetype, host, latest_raw, source
| table AppId, index, sourcetype, missing_field, host, latest_raw, source
| where missing_field!= ""
