topic Re: Can you help me get my subsearch to work? in Splunk Search

Can you help me get my subsearch to work?

keiran_harris — Sun, 30 Dec 2018 04:37:33 GMT

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.

Re: Can you help me get my subsearch to work?

renjith_nair — Sun, 30 Dec 2018 05:16:11 GMT

@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")

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

Re: Can you help me get my subsearch to work?

keiran_harris — Sun, 30 Dec 2018 05:54:49 GMT

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?

Re: Can you help me get my subsearch to work?

renjith_nair — Sun, 30 Dec 2018 06:57:14 GMT

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

Re: Can you help me get my subsearch to work?

keiran_harris — Wed, 02 Jan 2019 01:50:40 GMT

thanks!! that worked. Never used eventstats before - good to know!

Re: Can you help me get my subsearch to work?

renjith_nair — Wed, 02 Jan 2019 03:10:03 GMT

@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