Can you help me create the SPL for a report based on multiple columns?

[azaki]

hey all,

Currently, i'm working on an SPL in Splunk to create a dashboard based on a specific time fields.

We have a table with 4 date columns of interest, let's say their names like that : X, Y, Z, W... some of the values at each column can be null or in this format YYYY-MM-DD.

For each unique month of each column, we need to aggregate the number of X records, Y records. Z records and W records
For example if we have the following data sample:

We need to have a report like that ...
At November ==> number of X records: 1 , number of Y records: 1, number of Z records: 0 , number of W records: 0
At December ==> number of X records: 0, number of Y records: 1, number of Z records: 1 , number of W records: 1

[macadminrohit]

Below is the search i created taking dummy data as per your requirement :

| makeresults 
| eval Text="X:2018-11-01:NULL:NULL,Y:NULL:2018-11-03:2018-12-02,Z:NULL:2018-12-05:NULL,W:NULL:NULL:2018-12-10" 
| makemv Text delim="," 
| mvexpand Text 
| eval field_Name=mvindex(split(Text,":"),0) 
| rex field=Text "(X|Y|Z|W)\:(?<date>.*)" 
| makemv date delim=":" 
| mvexpand date 
| table _time date field_Name 
| eval Month=strptime(date,"%Y-%m-%d") 
| eval Month=strftime(Month,"%b") | fillnull Month value=0 | chart count(field_Name) as count over Month by field_Name | search Month!=0

Let us know if it works.

[somesoni2 · Accepted solution]

GIve this a try

your current search giving fields X Y Z W
| eval temp=1
| untable temp month column
| eval month=substr(month,1,7)
| chart count over month by column
| where month!="NULL"