Splunk Search
×

Are you a member of the Splunk Community?

Sign in or Register with your Splunk account to get your questions answered, access valuable resources and connect with experts!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ask a Question

Need to see data for the months with no events as zero

mbasharat
Builder
‎04-24-2025 07:23 AM

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:

SystemGroupScanMonthScanYearEnvironmentTOTAL1_LOW2_MEDIUM3_HIGH4_CRITICAL
A1234GSS-27220253_TEST21622815531
A1234GSS-27320253_TEST43045630862
A1234GSS-27120254_DEV00000
A1234GSS-27220254_DEV22222816131
A1234GSS-27320254_DEV44445632262
A1234GSS-27420254_DEV00000
A1234GSS-27520254_DEV00000
A1234GSS-27620254_DEV00000
A1234GSS-27720254_DEV00000
A1234GSS-27820254_DEV00000
A1234GSS-27920254_DEV00000
A1234GSS-271020254_DEV00000
A1234GSS-271120254_DEV00000
A1234GSS-271220254_DEV00000



Desired Output:

SystemGroupScanMonthScanYearEnvironmentTOTAL1_LOW2_MEDIUM3_HIGH4_CRITICAL
A1234GSS-27120253_TEST00000
A1234GSS-27220253_TEST221342140
A1234GSS-27320253_TEST43045630862
A1234GSS-271020243_TEST00000
A1234GSS-271120243_TEST00000
A1234GSS-271220243_TEST51202
A1234GSS-27120254_DEV105221
A1234GSS-27220254_DEV00000
A1234GSS-27320254_DEV00000
A1234GSS-271020244_DEV124323
A1234GSS-271120244_DEV2010523
A1234GSS-271220244_DEV00000
Labels (3)
Labels
0 Karma
Reply

mbasharat
Builder
‎04-24-2025 09:49 AM

@ITWhisperer 
I made adjustments as you suggested above and getting below results. Using one system and group sample. Running search for 6 months and need to see 0 for the months with no data/events. Above adjustments give only the months with data.

SystemGroupScanMonthScanYearEnvironmentTOTAL1_LOW2_MEDIUM3_HIGH4_CRITICAL
A1234GSS-27220253_TEST21622815531
A1234GSS-27320253_TEST43045630862
A1234GSS-27220254_DEV22222816131
A1234GSS-27320254_DEV44445632262

 

Needed:

SystemGroupScanMonthScanYearEnvironmentTOTAL1_LOW2_MEDIUM3_HIGH4_CRITICAL
A6020BGSS-27120253_TEST00000
A6020BGSS-27220253_TEST21622815531
A6020BGSS-27320253_TEST43045630862
A6020BGSS-27120254_DEV00000
A6020BGSS-27220254_DEV22222816131
A6020BGSS-27320254_DEV44445632262
A6020BGSS-271020243_TEST00000
A6020BGSS-271120253_TEST00000
A6020BGSS-271220263_TEST00000
A6020BGSS-271020274_DEV00000
A6020BGSS-271120284_DEV00000
A6020BGSS-271220294_DEV00000
0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎04-24-2025 10:09 AM

Probably the best way to include "missing" times is to use timechart. However, it is difficult to advise how you might use this without seeing your events. Please share your events (anonymised, of course).

0 Karma
Reply

mbasharat
Builder
‎04-24-2025 10:34 AM

Below is one sanitized raw test event:

2025-03-18 13:03:07.000, ID="484294162", Documentable="No", System="A1234", Group="GSS-27", Environment="3 TEST", Datasource="abcd.test.com", DBMSProduct="MS SQL SERVER", FindingType="Pass", SeverityCode="2 HIGH", SeverityScore="8.0", TestID="0000", TestName="SQL Server must generate audit records when unsuccessful attempts to modify categorized information occur.", TestDescription="Changes in categories of information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. If auditing the modification of data classifications is not required, this is not applicable.", FindingDescription="Auditing unsuccessful attempts to modify categorized information is set up correctly.", TestResultID="123456789101112131415", RemediationRule="1", RemediationAssignment="N/A", RemediationAnalysis="This test passed. No remediation necessary.", RemediationGuidance="No action required.", ExternalReference="STIG_Reference - SQL6-D0-014000 : STIG_SRG - SRG-APP-000498-DB-000347", VersionLevel="16.0", PatchLevel="0000", Reference="Sample14", VulnerabilityType="CONF", ScanTimestamp="2025-03-18 09:03:07.0000000", FirstExecution="2022-12-06 10:08:32.0000000", LastExecution="2025-03-18 09:03:35.0000000", CurrentScore="Pass", CurrentScoreSince="2022-12-06 10:08:32.0000000", CurrentScoreDays="833", AcknowledgedServiceAccount="No", SecurityAssessmentName="A1234_TEST (MS SQL SERVER)", CollectorID="testcollector", ScanYear="2025", ScanMonth="3", ScanDay="18", ScanCycle="2", Description="A1234;TEST;GSS-27", Host="12345.sample.test.com", Port="1234"

0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎04-24-2025 08:14 AM

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
0 Karma
Reply
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Take the Survey

Can’t make it to .conf25? Join us online!

Watch Global Broadcast
Get Updates on the Splunk Community!

Can’t Make It to Boston? Stream .conf25 and Learn with Haya Husain

Boston may be buzzing this September with Splunk University and .conf25, but you don’t have to pack a bag to ...

Splunk Lantern’s Guide to The Most Popular .conf25 Sessions

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Unlock What’s Next: The Splunk Cloud Platform at .conf25

In just a few days, Boston will be buzzing as the Splunk team and thousands of community members come together ...