How to convert tabular data to time series data?

[hmallett]

I have a lookup file, which is of the format:

"Department", "Jan FY20", "Feb FY20", "Mar FY20", "Apr FY20"
"Sales", "12", "15", "18", "17"
"HR", "7", "5", "6", "11"

Over time, the number of columns will increase, and their names may change, but they will always contain "FY".

What I want to do is return the data in the form:

department_name, month_name, value

I.e.

Sales, Jan FY20, 12
Sales, Feb FY20, 15
Sales, Mar FY20, 18
Sales, Apr FY20, 17
HR, Jan FY20, 7
HR, Feb FY20, 5
HR, Mar FY20, 6
HR, Apr FY20, 11

I'm sure that there's a simple function to do this (at search time), but I can't work it out.

What is the best way to do this?

[hmallett · Accepted solution]

I'm answering my own question here, but I'm posting it to show how simple the answer is when you can find the right Splunk function to use!

untable "converts results from a tabular format to a format similar to stats output"

So in my case I just needed to append to my search:


| untable Department month_name value
| rename Department as department_name


Thanks to Kamlesh for the inspiration, and to4kawa for putting that command in his answer.

[to4kawa]

| makeresults
| eval _raw="\"Department\", \"Jan FY20\", \"Feb FY20\", \"Mar FY20\", \"Apr FY20\"
\"Sales\", \"12\", \"15\", \"18\", \"17\"
\"HR\", \"7\", \"5\", \"6\", \"11\""
| multikv noheader=t 
| table _raw
| rename COMMNET as "this is sample. please check result"

| rename COMMENT as "this is logic, try from here"
| rex max_match=0 "\"(?<tmp>.*?)\""
| eval department_name=mvindex(tmp,0), unit=mvindex(tmp,1,-1)
| streamstats count as session
| eventstats list(eval(if(session=1,unit,NULL))) as Department
| eval tmp=mvzip(Department,unit)
| mvexpand tmp
| eval month_name=mvindex(split(tmp,","),0), value=mvindex(split(tmp,","),1)
| where month_name!=value
| table department_name month_name value

please make lookup file header to _raw

@kamlesh_vaghela 's method:

| makeresults 
| eval _raw="Department, Jan_FY20, Feb_FY20, Mar_FY20, Apr_FY20, May_FY20
Sales,12,15,18,17,10
HR,7,5,6,11" 
| multikv forceheader=1 
| table Department, Jan_FY20, Feb_FY20, Mar_FY20, Apr_FY20,May_FY20 
| rename comment as "This is for data generation only" 
| untable Department month_name value
| rename Department as department_name

It's pretty easy.

[hmallett]

Upvoted for including the "untable" command, which turned out to be exactly the function I needed.

[kamlesh_vaghela]

@hmallett

Can you please try this?

YOUR_SEARCH | fillnull value=0 
| eval Data=""
| foreach *FY* 
    [ eval Data=if(Data="","<<FIELD>>=".<<FIELD>>, Data."|"."<<FIELD>>=".<<FIELD>>) ] 
| eval Data=split(Data,"|")
 | mvexpand Data
 | rex field=Data "(?<month_name>.+)=(?<value>\d+)" | table Department month_name value

Sample Search:

| makeresults 
| eval _raw="Department, Jan_FY20, Feb_FY20, Mar_FY20, Apr_FY20, May_FY20
 Sales,12,15,18,17,10
 HR,7,5,6,11" 
| multikv forceheader=1 
| table Department, Jan_FY20, Feb_FY20, Mar_FY20, Apr_FY20,May_FY20 
| rename comment as "This is for data generation only" 
| fillnull value=0 
| eval Data=""
| foreach *FY* 
    [ eval Data=if(Data="","<<FIELD>>=".<<FIELD>>, Data."|"."<<FIELD>>=".<<FIELD>>) ] 
| eval Data=split(Data,"|")
 | mvexpand Data
 | rex field=Data "(?<month_name>.+)=(?<value>\d+)" | table Department month_name value

Thanks

[hmallett]

Thanks Kamlesh, I can see the approach you are taking, but when I run the search, I get errors "Failed to parse templatized search for field..." for each FY field.

[hmallett]

Upvoted for the approach