My search operation consists of two parts Part 1: This job runs every 6 hours and keeps appending to the results obtained to a csv file Query1-> index=INDEXA earliest=-6h@h latest=@h sourcetype=ABC "service=randomservice" (api_name=API1 OR api_name=API2 ) [search index=INDEXA earliest=-6h@h latest=@h sourcetype=ABC "service=randomservice" (api_name=API1 OR api_name=API2 ) | search XYZ= DEF | fields COMMONID | dedup COMMONID ] | stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by COMMONID | fillnull PQR value="NULL" | sort _time | outputcsv append=true testCSV.csv Part2: I need to extract the values from the csv within a specified time period. For example all the events between earliest = -1d@d and latest=@d . How do i achieve this, i'm unable to figure this out? Query2-> | inputcsv testCSV.csv | "What query do i need to give here to achieve the desired results?" ... View more

I am dealing with a massive dataset, i need to perform a join operation on a commonid. I have tried multiple variations of the query but i am facing issues with each of them. It would be great if anyone can provide a solution(Modifying the configuration files is not an option). Query Version 1: This query is taking up too much of memory and the query is auto-finalizing after reaching 1GB data limit, consequently the results are inaccurate. index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 ) | stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by COMMONID | fillnull PQR value="NULL" | search XYZ=DEF | timechart span=1d count by PQR Query Version 2: This query appears to be more efficient, but I am getting "[subsearch]: Search auto-finalized after time limit (60 seconds) reached" and consequently i am not able to obtain the accurate results. index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 ) [search index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 ) | search XYZ=DEF | fields COMMONID | dedup COMMONID] | stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by COMMONID | fillnull PQR value="NULL" | timechart span=1d count by PQR Query Version 3: This query will probably give accurate results, but it is very inefficient will take about 20 hours to finish execution. index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 ) | search (PQR=* OR NOT PQR=*) | join COMMONID [search index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 ) | search XYZ=DEF ] | stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by COMMONID | fillnull PQR value="NULL" | timechart span=1d count by PQR ... View more

Query : index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 ) | stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by ABC | fillnull PQR value="NULL" | search XYZ=DEF | timechart span=1d count by PQR ... View more