Hi guys,
i need help with a search. I believe it's a subsearch that i need (I need a variable output of one search to feed another search), but I cant make it work.
Basically, i have written code that polls a weather forecast API and spits back JSON, which Splunk gobbles up. Trouble is, the API call is made several times a day, which means i get several, duplicate predictions in my data set. I only want to take the latest data, and ignore all previous.
Here is my search which works well in giving me the table i need, when i have a clean index (i.e. only one API poll has been ingested thus far):
sourcetype=_jsonFUTURE BOMPREDdate | eval Day = strftime(_time,"%a") | eval Date = strftime(_time,"%F") | sort _time | table Day, Date, BOMPREDrainChance, BOMPREDrainMM, BOMPREDdescBrief, BOMPREDdescDetail | rename BOMPREDrainChance as "Rain%", BOMPREDrainMM as "RainMM", BOMPREDdescBrief as "ForecastBrief", BOMPREDdescDetail as "ForecastDetail"
but when the API poll script has run twice, for instance, the table now has duplicates as shown below:
In my JSON data set, i have now included a field ive called 'BOMPREDapiPollTime' which is an epoch time that the script was executed...so the 7 JSON events that get ingested each time the script is run, all share the same value 'BOMPREDapiPollTime' as shown below.
So, all i believe i need to do is:
a) find that latest timestamp of 'BOMPREDapiPollTime' - which i can do with the search 'sourcetype=_jsonFUTURE BOMPREDdate | stats latest(BOMPREDapiPollTime) as pollTime'
b) feed that into my working search (pictured above) - i believe with a subsearch...
I have tried variants of the below without luck:
sourcetype=_jsonFUTURE BOMPREDdate [search sourcetype=_jsonFUTURE BOMPREDdate | stats latest(BOMPREDapiPollTime) as pollTime] | eval Day = strftime(_time,"%a") | eval Date = strftime(_time,"%F") | sort _time | table Day, Date, BOMPREDrainChance, BOMPREDrainMM, BOMPREDdescBrief, BOMPREDdescDetail | rename BOMPREDrainChance as "Rain%", BOMPREDrainMM as "RainMM", BOMPREDdescBrief as "ForecastBrief", BOMPREDdescDetail as "ForecastDetail"
BUT I cant make it work! (i always get zero results).
Any help would be greatly appreciated.
I'm sure it's something stupid I'm doing.
thanks in advance guys!
Keiran.
@keiran_harris,
Updated:
sourcetype=_jsonFUTURE BOMPREDdate | eval Day = strftime(_time,"%a") | eval Date = strftime(_time,"%F")
| table Day, Date, BOMPREDrainChance, BOMPREDrainMM, BOMPREDdescBrief, BOMPREDdescDetail,BOMPREDapiPollTime
|eventstats latest(BOMPREDapiPollTime) as latestPollTime
|where BOMPREDapiPollTime=latestPollTime
| rename BOMPREDrainChance as "Rain%", BOMPREDrainMM as "RainMM", BOMPREDdescBrief as "ForecastBrief", BOMPREDdescDetail as "ForecastDetail"
If you are looking for the latest records (only one record ) for the day, try this
sourcetype=_jsonFUTURE BOMPREDdate |eval Date = strftime(_time,"%F")
|stats latest(BOMPREDrainChance) as "Rain%",latest(BOMPREDrainMM) as "RainMM",
latest(BOMPREDdescBrief) as ForecastBrief,latest(BOMPREDdescDetail) as ForecastDetail by Date
|eval Day=strftime(strptime(Date,"%Y-%m-%d"),"%a")
Or
If you are already sorting , sort in reverse order and get the first
value
sourcetype=_jsonFUTURE BOMPREDdate| eval Date = strftime(_time,"%F") | sort - _time
|stats first(BOMPREDrainChance) as "Rain%",first(BOMPREDrainMM) as "RainMM",
first(BOMPREDdescBrief) as ForecastBrief,first(BOMPREDdescDetail) as ForecastDetail by Date
|eval Day=strftime(strptime(Date,"%Y-%m-%d"),"%a")
Hi thanks for your help, but sorry if i didnt explian well.... i dont need just a single record.... each time the script runs, it generates 7 JSON events. And the table needs all 7, but only the latest 7. Each batch of 7 JSON events share the same API poll epoch time. Hopefully that clears up things?
@keiran_harris , alright, so you just need the records which matches with the latest polltime and this polltime is same for all events (7).
Try this,
sourcetype=_jsonFUTURE BOMPREDdate | eval Day = strftime(_time,"%a") | eval Date = strftime(_time,"%F") | table Day, Date, BOMPREDrainChance, BOMPREDrainMM, BOMPREDdescBrief, BOMPREDdescDetail,BOMPREDapiPollTime
|eventstats latest(BOMPREDapiPollTime) as latestPollTime
|where BOMPREDapiPollTime=latestPollTime
| rename BOMPREDrainChance as "Rain%", BOMPREDrainMM as "RainMM", BOMPREDdescBrief as "ForecastBrief", BOMPREDdescDetail as "ForecastDetail"
thanks!! that worked. Never used eventstats before - good to know!
@keiran_harris, glad that it worked. If you do not have any further questions, you may accept it as answer to close the thread. I have updated the answer section