Below is the raw data that we have and enters Splunk
Raw Data and each of the application holidays enters as a single record for each date for each application with a unique source type
Raw Data
App 1 Holidays App 2 Holidays App 3 Holidays App 4 Holidays App 5 Holidays
Date1 Date1 Date1 Date1 Date1
Date2 Date2 Date3 Date2 Date2
Date4 Date3 Date4 Date4 Date4
Date5 Date4 Date5 Date5 Date5
Date6 Date5 Date6 Date6 Date6
Date7 Date6 Date7 Date7 Date7
Date8 Date7 Date8 Date8 Date8
Date9 Date8 Date9 Date9 Date9
Date10 Date9 Date10 Date10 Date10
Date11 Date10 Date11 Date11 Date11
Date11
Am trying to build a dashboard wherein it compares all the holidays and gets a list of matched and unmatched holidays. Expectations as below
Part I -
Matched Holidays
Holidays Application 1 Application 2 Application 3 Application 4 Application 5
Date1 Matched Matched Matched Matched Matched
Date4 Matched Matched Matched Matched Matched
Date5 Matched Matched Matched Matched Matched
Date6 Matched Matched Matched Matched Matched
Date7 Matched Matched Matched Matched Matched
Date8 Matched Matched Matched Matched Matched
Date9 Matched Matched Matched Matched Matched
Date10 Matched Matched Matched Matched Matched
Date11 Matched Matched Matched Matched Matched
Part II
Un Matched Holidays
Holidays Application 1 Application 2 Application 3 Application 4 Application 5
Date2 Matched Matched No Match Matched Matched
Date3 No Match Matched Matched No Match No Match
I was able to do the needed to create the report for matching holidays(Part I ) but was unable to manage the unmatched holidays.
MFSOURCETYPE=*_HOLIDAY |replace "DATA" with "" in DATA as DATA | table DATA MFSOURCETYPE | stats COUNT as COUNT by DATA | table DATA COUNT |
eval app1 = if(COUNT = "3" ,"Match", "No Match") | eval app2 = if(COUNT = "3" ,"Match", "No Match") | eval app3 = if(COUNT = "3" ,"Match", "No Match") |
table DATA app1 app2 app3 | where app1 = "Match"
Sample JSON Data as below
{ [-]
DATA: DATE1 ,
MFSOURCETYPE: App1_HOLIDAY
}
{ [-]
DATA: DATE2 ,
MFSOURCETYPE: App1_HOLIDAY
}
{ [-]
DATA: DATE1,
MFSOURCETYPE: APP2_HOLIDAY
}
NOTE – Date Format – “DD-MM-YYYY”
Give this a try
Part I - Matched Holidays
MFSOURCETYPE=*_HOLIDAY |replace "DATA" with "" in DATA as DATA
| table DATA MFSOURCETYPE | dedup DATA MFSOURCETYPE
| chart count over DATA by MFSOURCETYPE
| foreach * [eval "<<FIELD>>"=case('<<FIELD>>'=0,"No Match", '<<FIELD>>'=1,"Matched",true(),'<<FIELD>>') ]
| eval keep="Y"
| foreach * [eval keep=if('<<FIELD>>'="No Match","N",keep)]
| where keep="Y" | fields - keep
Part II - Un Matched Holidays
MFSOURCETYPE=*_HOLIDAY |replace "DATA" with "" in DATA as DATA
| table DATA MFSOURCETYPE | dedup DATA MFSOURCETYPE
| chart count over DATA by MFSOURCETYPE
| foreach * [eval "<<FIELD>>"=case('<<FIELD>>'=0,"No Match", '<<FIELD>>'=1,"Matched",true(),'<<FIELD>>') ]
| eval keep="Y"
| foreach * [eval keep=if('<<FIELD>>'="No Match","N",keep)]
| where keep="N" | fields - keep
Give this a try
Part I - Matched Holidays
MFSOURCETYPE=*_HOLIDAY |replace "DATA" with "" in DATA as DATA
| table DATA MFSOURCETYPE | dedup DATA MFSOURCETYPE
| chart count over DATA by MFSOURCETYPE
| foreach * [eval "<<FIELD>>"=case('<<FIELD>>'=0,"No Match", '<<FIELD>>'=1,"Matched",true(),'<<FIELD>>') ]
| eval keep="Y"
| foreach * [eval keep=if('<<FIELD>>'="No Match","N",keep)]
| where keep="Y" | fields - keep
Part II - Un Matched Holidays
MFSOURCETYPE=*_HOLIDAY |replace "DATA" with "" in DATA as DATA
| table DATA MFSOURCETYPE | dedup DATA MFSOURCETYPE
| chart count over DATA by MFSOURCETYPE
| foreach * [eval "<<FIELD>>"=case('<<FIELD>>'=0,"No Match", '<<FIELD>>'=1,"Matched",true(),'<<FIELD>>') ]
| eval keep="Y"
| foreach * [eval keep=if('<<FIELD>>'="No Match","N",keep)]
| where keep="N" | fields - keep
Just to the point and very perfect. Many Thanks
Would like to add a small feature, but sorry to bug you.
Instead of DATA COLUMN in the table , can we have the report to look like something below which will just highlight the holidays in the application calendar
App 1 Holidays App 2 Holidays App 3 Holidays App 4 Holidays App 5 Holidays
Date1 Date1 Date1 Date1 Date1
Date2 Date2 Date2 Date2
Date3 Date3
Date4 Date4 Date4 Date4 Date4
Date5 Date5 Date5 Date5 Date5
Date6 Date6 Date6 Date6 Date6
Date7 Date7 Date7 Date7 Date7
Date8 Date8 Date8 Date8 Date8
Date9 Date9 Date9 Date9 Date9
Date10 Date10 Date10 Date10 Date10
Date11 Date11 Date11 Date11 Date11