Hi all, I have a situation. Below is my search. Search needs to produce past 6 months of report. The goal is to produce ZEROs for the months with no events. However, below search is producing result...
See more...
Hi all, I have a situation. Below is my search. Search needs to produce past 6 months of report. The goal is to produce ZEROs for the months with no events. However, below search is producing results with ZEROs for the whole year instead of just 6 months. How to make it do only for 6 months? Thank you! Search: index=sample_index sourcetype=sample_sourcetype AcknowledgedServiceAccount="No" System="ABC" | eval ScanMonth_Translate=case( ScanMonth="1","January", ScanMonth="2","February", ScanMonth="3","March", ScanMonth="4","April", ScanMonth="5","May", ScanMonth="6","June", ScanMonth="7","July", ScanMonth="8","August", ScanMonth="9","September", ScanMonth="10","October", ScanMonth="11","November", ScanMonth="12","December") | fields ID, System, GSS, RemediationAssignment, Environment, SeverityCode, ScanYear, ScanMonth | fillnull value="NULL" ID, System, GSS, RemediationAssignment, Environment, SeverityCode, ScanYear, ScanMonth | foreach System Group Environment ScanMonth, ScanYear, SeverityCode [| eval <<FIELD>> = split(<<FIELD>>, "\n") | eval <<FIELD>> = split(<<FIELD>>, "\n") | eval <<FIELD>> = split(<<FIELD>>, "\n") | eval <<FIELD>> = split(<<FIELD>>, "\n") | eval <<FIELD>> = split(<<FIELD>>, "\n") | eval <<FIELD>> = split(<<FIELD>>, "\n") ] | stats count AS Total_Vulnerabilities BY ScanMonth, ScanYear, System, Group, Environment, SeverityCode | fields System, Group, ScanMonth, ScanYear, Environment, SeverityCode, Total_Vulnerabilities | stats values(eval(if(SeverityCode="1 CRITICAL",Total_Vulnerabilities, null()))) as "4_CRITICAL" values(eval(if(SeverityCode="2 HIGH",Total_Vulnerabilities, null()))) as "3_HIGH" values(eval(if(SeverityCode="3 MEDIUM",Total_Vulnerabilities, null()))) AS "2_MEDIUM" values(eval(if(SeverityCode="4 LOW",Total_Vulnerabilities, null()))) as "1_LOW" sum(Total_Vulnerabilities) AS TOTAL by System, Group, ScanMonth, ScanYear, Environment | fillnull value="0" 4_CRITICAL, 3_HIGH, 2_MEDIUM, 1_LOW | fields System, Group, Environment, ScanMonth, ScanYear, 4_CRITICAL, 3_HIGH, 2_MEDIUM, 1_LOW, TOTAL | replace "*PROD*" WITH "1_PROD" IN Environment | replace "*DR*" WITH "2_DR" IN Environment | replace "*TEST*" WITH "3_TEST" IN Environment | replace "*DEV*" WITH "4_DEV" IN Environment | sort 0 + System, GSS, Environment, ScanMonth, ScanYear | append [| makeresults | eval ScanMonth="1,2,3,4,5,6,7,8,9,10,11,12" | eval 4_CRITICAL="0" | eval 3_HIGH="0" | eval 2_MEDIUM="0" | eval 1_LOW="0" | eval TOTAL="0" | makemv delim="," ScanMonth | stats count by ScanMonth, 4_CRITICAL, 3_HIGH, 2_MEDIUM, 1_LOW, TOTAL | fields - count ] | fillnull value="0" 4_CRITICAL, 3_HIGH, 2_MEDIUM, 1_LOW, TOTAL | filldown | stats sum(TOTAL) AS TOTAL sum(1_LOW) AS 1_LOW sum(2_MEDIUM) AS 2_MEDIUM sum(3_HIGH) AS 3_HIGH sum(4_CRITICAL) AS 4_CRITICAL by System, Group, ScanMonth, ScanYear, Environment | sort 0 + System, Group, Environment, ScanMonth, ScanYear Output: System Group ScanMonth ScanYear Environment TOTAL 1_LOW 2_MEDIUM 3_HIGH 4_CRITICAL A1234 GSS-27 2 2025 3_TEST 216 2 28 155 31 A1234 GSS-27 3 2025 3_TEST 430 4 56 308 62 A1234 GSS-27 1 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 2 2025 4_DEV 222 2 28 161 31 A1234 GSS-27 3 2025 4_DEV 444 4 56 322 62 A1234 GSS-27 4 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 5 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 6 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 7 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 8 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 9 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 10 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 11 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 12 2025 4_DEV 0 0 0 0 0 Desired Output: System Group ScanMonth ScanYear Environment TOTAL 1_LOW 2_MEDIUM 3_HIGH 4_CRITICAL A1234 GSS-27 1 2025 3_TEST 0 0 0 0 0 A1234 GSS-27 2 2025 3_TEST 221 3 4 214 0 A1234 GSS-27 3 2025 3_TEST 430 4 56 308 62 A1234 GSS-27 10 2024 3_TEST 0 0 0 0 0 A1234 GSS-27 11 2024 3_TEST 0 0 0 0 0 A1234 GSS-27 12 2024 3_TEST 5 1 2 0 2 A1234 GSS-27 1 2025 4_DEV 10 5 2 2 1 A1234 GSS-27 2 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 3 2025 4_DEV 0 0 0 0 0 A1234 GSS-27 10 2024 4_DEV 12 4 3 2 3 A1234 GSS-27 11 2024 4_DEV 20 10 5 2 3 A1234 GSS-27 12 2024 4_DEV 0 0 0 0 0