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
SplunkTrust
SplunkTrust

@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")
Happy Splunking!
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
SplunkTrust
SplunkTrust

@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"
Happy Splunking!
0 Karma

keiran_harris
Path Finder

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...