Dashboards & Visualizations

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

azaki
Explorer

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:
alt text

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

0 Karma
1 Solution

somesoni2
Revered Legend

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" 

View solution in original post

0 Karma

macadminrohit
Contributor

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
Revered Legend

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" 
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...