Assuming you want all possible combinations of System Group and Environment for each of the possible ScanMonth and ScanYear already present in your results, you could try something like this index=s...
See more...
Assuming you want all possible combinations of System Group and Environment for each of the possible ScanMonth and ScanYear already present in your results, you could try something like this 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
| appendpipe
[| stats values(System) as System values(Group) as Group values(Environment) as Environment by ScanMonth ScanYear
| eventstats values(System) as System values(Group) as Group values(Environment) as Environment
| mvexpand System
| mvexpand Group
| mvexpand Environment
| fillnull value="0" 4_CRITICAL, 3_HIGH, 2_MEDIUM, 1_LOW, TOTAL
]
| 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