Splunk Search

Searching events within a time range from csv file

tareddy
Explorer

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

Tags (1)
0 Karma

nawneel
Communicator

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

0 Karma

sbbadri
Motivator

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

0 Karma

tareddy
Explorer

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

0 Karma

sbbadri
Motivator

| 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

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...