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?"
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
You can put second query in saved search set earliest and latest.
I'm facing difficulties in extracting the time from the csv file. Earliest and latest keywords aren't working.
| 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