Splunk Search

Comparing arbitrary # of columns values similar to xyseries?

grantsmiley
Path Finder

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.

Tags (1)

DalJeanis
SplunkTrust
SplunkTrust

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
0 Karma

grantsmiley
Path Finder

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.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.