Searching events within a time range from csv file

[tareddy]

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?"

[nawneel]

Hi @tareddy
| inputcsv testCSV.csv , you will get date(human readable or EPOCH, However you had put them while creating CSV as STRING) , so u need to format string to time using strptime and strftime , once you have it in timeformat. you can use filters to get your desired results

[sbbadri]

You can put second query in saved search set earliest and latest.

[tareddy]

I'm facing difficulties in extracting the time from the csv file. Earliest and latest keywords aren't working.

[sbbadri]

| stats earliest(_time) as earliestTime , latest(_time) as latestTime, values(XYZ) AS XYZ, values(PQR) AS PQR by COMMONID | eval earliest=strftime(earliestTIme,"%Y-%m-%d %H:%M:%S") | eval latest=strftime(latestTime,"%Y-%m-%d %H:%M:%S")| fillnull PQR value="NULL" | sort _time| outputcsv append=true testCSV.csv

second query

| inputcsv testCSV.csv | table earliest latest