Suppose I have a data set with a metric, let's say for example, it contains the average # of stamps licked per day by a person in a bulk mailer operation with an unknown, inconsistent # of stamp lickers.
ideally the data would be like this:
StampLicker Date Produced
Rita , 8/1/2018 , 15
Henry , 8/1/2018 , 14
Stefan , 8/1/2018 , 15
Rita , 8/2/2018 ,16
Henry , 8/2/2018 ,14
Stefan , 8/2/82018 ,15
Amy , 8/3/2018 , 16
Henry , 8/3/2018 , 14
Stefan , 8/3/82018 , 15
But unfortunately I have it like this:
Date AMY RITA HENRY STEFAN
8/1/2018 , ,15 ,14,15
8/2/2018, ,16,14,15
8/3/2018,16 , 14,15
These have run for a while, perhaps years. Stamp lickers have come and gone. I'm interested in seeing metrics for most recent day as well as historical average to produce a chart like this:
Chart for most recent day
AMY RITA HENRY STEFAN
Amy X NA -2 1
Rita NA X 2 1
Henry -2 -2 X -1
Stefan -1 -1 1 X
and perhaps a trend line indicating success or failure of the stamp licking training program when a new stamp licker is hired. Any thoughts on how to compute that table without doing|eval HenryVSAmy=Henry-Amy and so on because I don't know how many/what the columns will be in advance necessarily. It's similar to the XYSeries function but not.
You want the untable
command. It is the inverse of xyseries
, and gives you exactly the transformation you are asking for.
| untable date licker licked
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Untable
And here's a run-anywhere example
| makeresults
| eval mydata="8/1/2018 RITA=15 HENRY=14 STEFAN=15!!!!8/2/2018 RITA=16 HENRY=14 STEFAN=15!!!!8/3/2018 AMY=16 HENRY=14 STEFAN=15"
| makemv delim="!!!!" mydata
| mvexpand mydata
| eval _time=strptime(substr(mydata,1,8),"%m/%d/%Y")
| eval mydata=substr(mydata,9)
| rex field=mydata max_match=0 "\b(?<onelicker>\S*)\b"
| fields - mydata
| mvexpand onelicker
| rex field=onelicker "(?<StampLicker>[^=]*)=(?<Produced>\d*)$"
| fields - onelicker
| chart sum(Produced) by _time StampLicker
| rename COMMENT as "The above just enters your data as given"
| untable _time StampLicker Produced
That untable thing is really good, thanks for that. I'm curious how/if that differs between |stats sum(Produced) by ASOFDATE Licker
which seems to be same only with one step.
Question remains, how to generate this table:
AMY RITA HENRY STEFAN
Amy X NA -2 1
Rita NA X 2 1
Henry -2 - 2 X -1
Stefan -1 -1 1 X
Where the cells are the difference between the combinations of lickers.