Splunk Search

How to convert tabular data to time series data?

hmallett
Path Finder

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?

0 Karma
1 Solution

hmallett
Path Finder

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.

View solution in original post

0 Karma

hmallett
Path Finder

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.

0 Karma

to4kawa
Ultra Champion
| 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
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
Path Finder

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.

0 Karma

hmallett
Path Finder

Upvoted for the approach

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...