topic Re: Searching events within a time range from csv file in Splunk Search

Searching events within a time range from csv file

tareddy — Tue, 29 Sep 2020 15:03:59 GMT

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

Re: Searching events within a time range from csv file

sbbadri — Thu, 27 Jul 2017 03:18:47 GMT

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

Re: Searching events within a time range from csv file

tareddy — Thu, 27 Jul 2017 03:49:35 GMT

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

Re: Searching events within a time range from csv file

sbbadri — Tue, 29 Sep 2020 15:04:05 GMT

| 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

Re: Searching events within a time range from csv file

nawneel — Thu, 27 Jul 2017 08:36:37 GMT

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