How to do compound query with where clause in Splunk?

Question

I have the following data as example:

I want to find all events whose locations having had temperature above a threshold, say 80F sometimes.

Temperature=82.4, Location=xxx.165.152.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=84.2, Location=xxx.165.152.48, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=82.4, Location=xxx.165.154.21, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=82.4, Location=xxx.165.162.22, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=77.0, Location=xxx.165.164.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=75.2, Location=xxx.165.170.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=77.0, Location=xxx.165.208.12, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=73.4, Location=xxx.165.224.20, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=75.3, Location=xxx.165.52.13, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=77.9, Location=xxx.165.52.14, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=76.3, Location=xxx.165.54.24, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=83.8, Location=xxx.165.48.20, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=73.8, Location=xxx.165.36.21, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor

I might first find the subsets of locations with whom the temperatures having been above the threshold with the following:

| Temperature > 80
| fields Location
| dedup Location

I'd call the locations outcome of the query "hot_locations",

then I'd like to perform my eventual query:

| Location IN hot_locations

My question is what's the syntax of Splunk query language to express the specification? That is, how to express the embedded query and use its values to perform the eventual query.

In pseudo code, it might be like the following:

| let hot_locations = {| Temperature > 80
| fields Location
| dedup Location}
| Location IN hot_locations

What's the proper expression for it?

If it helps, I need the logic in dashboard, I'm thinking that I might use input panel's variable to express the value of hot_locations.

Answer 1

Something like this should do it (presumign the fields are properly broken-out already):

index=ndx sourcetype=srctp Temperature>80 
| eval sorttime=strptime(Time,"%a %B %d %Y %H:%M:%S %Z")
| stats values(Time) as Time by strptime Location Temperature Type
| fields - strptime

strptime will convert from your timestamp into epoch time ... which allows for better sorting.

That step may be unnecessary - depending on what your data actually looks like - I added it based on the sample you provided

If you also need to parse the fields, I'll add-onto the answer with regexes that will grab those

Answer 2

Splunk does not have the ability to label query results. You can do the equivalent with a subsearch, however.

index=foo [ search index=bar Temperature > 80 | fields Location | format ]