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
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"
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.
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"