topic Re: Issue with using json token as dropdown token in Dashboards & Visualizations

Issue with using json token as dropdown token

uhaba — Thu, 10 Sep 2020 16:24:07 GMT

I am polling the Alpaca stock API and getting the following json payload:

{"APPL":[{"t":1599753600,"o":12.72,"h":12.785,"l":12.72,"c":12.785,"v":524}],"AMZN":[{"t":1599753600,"o":13.99,"h":13.99,"l":13.99,"c":13.99,"v":200}],"GOOG":[{"t":1599753600,"o":33.69,"h":33.82,"l":33.69,"c":33.82,"v":3372}],"XOM":[{"t":1599753600,"o":37.67,"h":37.78,"l":37.66,"c":37.76,"v":10404}]}

Splunk is extracting the following fields for example:

XOM{}.c=37.76
XOM{}.h=37.78
XOM{}.l=37.66
XOM{}.o=37.67
XOM{}.t=1599753600
XOM{}.v=10404

I'm trying to timechart the different stocks 'c' values from yesterday where 'c' is the changing close value.

host="Alpaca:Stock" earliest=-1d@d+510m latest=-1d@d+15h
| timechart span=5m values(*{}.c)
| rename values(APPL{}.c) as APPL values(AMZN{}.c) as AMZN values(GOOG{}.c) as GOOG values(XOM{}.c) as XOM

I would like to add a dropdown select to choose from the various stocks coming in the payload so the timechart panel only displays the selected stock symbol data. I have been having trouble finding a way to either use the stock symbol in the array path or something like click.name2 as the token. the I've tried spath and not having any luck creating a new field to call as a token.

Any help on this is greatly appreciated!

Re: Issue with using json token as dropdown token

ITWhisperer — Thu, 10 Sep 2020 17:15:49 GMT

As a basic example, you could populate your dropdown from this sort of query

| makeresults | eval event="{\"APPL\":[{\"t\":1599753600,\"o\":12.72,\"h\":12.785,\"l\":12.72,\"c\":12.785,\"v\":524}],\"AMZN\":[{\"t\":1599753600,\"o\":13.99,\"h\":13.99,\"l\":13.99,\"c\":13.99,\"v\":200}],\"GOOG\":[{\"t\":1599753600,\"o\":33.69,\"h\":33.82,\"l\":33.69,\"c\":33.82,\"v\":3372}],\"XOM\":[{\"t\":1599753600,\"o\":37.67,\"h\":37.78,\"l\":37.66,\"c\":37.76,\"v\":10404}]}\n{\"XYZ\":[{\"t\":1599753600,\"o\":12.72,\"h\":12.785,\"l\":12.72,\"c\":12.785,\"v\":524}],\"ABC\":[{\"t\":1599753600,\"o\":13.99,\"h\":13.99,\"l\":13.99,\"c\":13.99,\"v\":200}],\"ASDF\":[{\"t\":1599753600,\"o\":33.69,\"h\":33.82,\"l\":33.69,\"c\":33.82,\"v\":3372}],\"APPL\":[{\"t\":1599753600,\"o\":37.67,\"h\":37.78,\"l\":37.66,\"c\":37.76,\"v\":10404}]}" | eval event=split(event,"\n") | mvexpand event --- above generates some data in event field | spath input=event | head 1 | fields - _* | foreach *.c [ eval name=split("<<MATCHSTR>>","{") | eval stock=mvappend(stock, mvindex(name,0)) | fields stock ]

Re: Issue with using json token as dropdown token

uhaba — Thu, 10 Sep 2020 17:57:27 GMT

Thanks for that! I'm finding it is coming back as one returned value of all the stock symbols. Should I use mvexpand() maybe? When searching this on it's own, I get the field of stock as a mvfield with all the stock symbols returned to the right.

host="Alpaca:Stock" sourcetype=_json
| spath input=_raw
| head 1
| fields - _*
| foreach *.c [ eval name=split("<<MATCHSTR>>","{") | eval stock=mvappend(stock, mvindex(name,0)) | fields stock ]

Re: Issue with using json token as dropdown token

uhaba — Thu, 10 Sep 2020 19:02:30 GMT

So I got your wonderful solution to populate the dropdown. Just needed to add mvexpand at the end.

host="Alpaca:Stock" sourcetype=_json | spath input=_raw | head 1 | fields - _* | foreach *.c [ eval name=split("<<MATCHSTR>>","{") | eval stock=mvappend(stock, mvindex(name,0)) | fields stock ] | mvexpand stock

I could still really use some help working out how to now map those fields to a token in the results from this search:

host="Alpaca:Stock" earliest=-1d@d+510m latest=-1d@d+15h | timechart span=5m values(*{}.c) as *

The search outputs the stock name, which is matched by * above, as the y values in the timechart table. So I need to somehow match the new dynamically populated token list from the search further up against this output. Generally I've done some key=$foo$ but b/c this is coming in as a mv json payload, the relevant stock name is embedded in the field name and I don't think it'll really work. I tried adding a "*{}.c=$close$" and mapped to close and it didn't work.

Re: Issue with using json token as dropdown token

ITWhisperer — Thu, 10 Sep 2020 20:09:48 GMT

Edit the source of your dashboard form so it is something like this:

<row> <panel> <title>Choose stocks</title> <input type="multiselect" token="stocks"> <label>Select one or more stock</label> <choice value="All">All</choice> <search> <query>host="Alpaca:Stock" sourcetype=_json | spath input=_raw | head 1 | fields - _* | foreach *.c [ eval name=split("<<MATCHSTR>>","{") | eval stock=mvappend(stock, mvindex(name,0)) | fields stock ] | mvexpand stock</query> </search> <fieldForLabel>stock</fieldForLabel> <fieldForValue>stock</fieldForValue> <delimiter>, </delimiter> <default>All</default> <change> <eval token="form.stocks">case(mvcount('form.stocks')=0,"All",mvcount('form.stocks')>1 AND mvfind('form.stocks',"All")>0,"All",mvcount('form.stocks')>1 AND mvfind('form.stocks',"All")=0,mvfilter('form.stocks'!="All"),1==1,'form.stocks')</eval> <eval token="stocks_choice">if(mvfind('form.stocks',"All")=0,"*.c",$stocks$)</eval> </change> </input> </panel> </row> <row> <panel> <chart> <search> <query>host="Alpaca:Stock" earliest=-1d@d+510m latest=-1d@d+15h | fields $stocks_choice$ | timechart span=5m values(*{}.c) as *</query> </search>

The query in the choice section adds your stock names to the dropdown. The delimiter put ", " between each selected stock name. The fields in the dashboard query limits the columns to be only the chosen stocks. All will allow all fields through

There may be other ways to filter the fields but see if this works for you

Re: Issue with using json token as dropdown token

uhaba — Mon, 14 Sep 2020 18:22:31 GMT

This is great! I had to flip the fields to below the timechart command but it's working well!