I want to generate a time chart that shows time on x-axis, results on y-axis and hue (legend) showing the different analytes. So far this what I have generated which is not the format I am looking for. I have the search code below. I probably do not need fieldformat but was thinking I needed the correct datatype. I am used to python Jupyter notebooks and am quite new to Splunk. Any help would be very appreciated. For example, I am showing a scatter plot from python that I can generate that mirrors what I am looking for in Splunk
|inputlookup $lookupToken$
|where _time <= $tokLatestTime$
|where _time >= $tokEarliestTime$
|search $lab_token$
|search $analyte_token$
|search $location_token$
|sort _time desc
|replace "ND" WITH 0 IN Results
|table _time, Results, Analyte
|fieldformat _time=strftime(_time, "%Y-%m-%d")
You can't do time on x-axis on a scatter plot, so the best way to do this is with a timechart and a bit of trickery with the data
Note you can do all your search statements in a single search command
| inputlookup $lookupToken$
| where _time <= $tokLatestTime$ AND time >= $tokEarliestTime$
| search $lab_token$ $analyte_token$ $location_token$
| replace "ND" WITH 0 IN Results
| timechart max(Result) as Result by Analyte
| append [
| inputlookup $lookupToken$
| where _time <= $tokLatestTime$ AND time >= $tokEarliestTime$
| search $lab_token$ $analyte_token$ $location_token$
| replace "ND" WITH 0 IN Results
| bin _time span=1d
| stats max(Result) as Result by _time Analyte
| eval Result=null()
]
| sort _time
| fields - Result
In the timechart, set the handling of null values to gaps.
See this post
Thanks for your help and the suggestion group search statements in single command that is very useful. I followed your suggestion and still am not getting a plot. Does this work with scatter plot or only line plot as I went to link you tagged and from there it stated to use line plot. Here is the code below and snips of what I am seeing.
|inputlookup $lookupToken$
|where _time <= $tokLatestTime$ AND _time >= $tokEarliestTime$
|search $lab_token$ $analyte_token$ $location_token
|replace "ND" WITH 0 IN Results
|timechart max(Results) as Results by Analyte
|append [
|inputlookup $lookupToken$
|where _time <= $tokLatestTime$ AND _time >= $tokEarliestTime$
|search $lab_token$ $analyte_token$ $location_token$
|replace "ND" WITH 0 IN Results
|bin _time span=1d
|stats max(Results) as Results by _time Analyte
|eval Results=null()
]
|sort _time
|fields - Results
Now if I remove the Results=null(), remove replacing ND with 0 and switch fields to Analyte instead of Results I get points again and something close to what I want but still off a bit. I am hoping to see the analytes on the legend. You have any further suggestions? I feel I am close.
|inputlookup $lookupToken$
|where _time <= $tokLatestTime$ AND _time >= $tokEarliestTime$
|search $lab_token$ $analyte_token$ $location_token
|timechart max(Results) as Results by Analyte
|append [
|inputlookup $lookupToken$
|where _time <= $tokLatestTime$ AND _time >= $tokEarliestTime$
|search $lab_token$ $analyte_token$ $location_token$
|bin _time span=1d
|stats max(Results) as Results by _time Analyte
]
|sort _time
|fields - Analyte
It must be done with a line chart, not scatter, as scatter will not handle time on the x-axis.
Can you post an example of what the data looks like
I would expect a series of rows with a _time column followed by a column for each Analyte and then the Result value in each cell for the _time/Analyte.
Not sure what happened, but logged in over the weekend and somehow I have what I want lol. Was getting frustrated on Thursday and have up but must have done something right. Here is the code... and image of graph and table. Thanks for all your help!!!
|inputlookup $lookupToken$
|where _time <= $tokLatestTime$
|where _time >= $tokEarliestTime$
|search $lab_token$
|search $analyte_token$
|search $location_token$
|sort _time desc
|fieldformat _time=strftime(_time, "%Y-%m-%d")
|table Analyte, _time, Results
|timechart span=1d max(Results) by Analyte
|eval Results=null()