Splunk Search

Can you help me get my subsearch to work?

keiran_harris
Path Finder

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:
alt text

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.
alt text

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.

0 Karma

renjith_nair
Legend

@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")
---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

keiran_harris
Path Finder

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?

0 Karma

renjith_nair
Legend

@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"
---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

keiran_harris
Path Finder

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

0 Karma

renjith_nair
Legend

@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

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...