Splunk Search

Showing rows and columns when they do not exist in the event data

aelliott
Motivator

I have a spreadsheet with a list of locations.
I have a list of Categories.
I have events of incidents with an office location on them that is the same as the office location on the spreadsheet,and categories that also are the same as the spreadsheet.

Data:
Incident1 Office A Category A
Incident2 Office A Category A
Incident3 Office B Category B
Incident4 Office D Category B

Spreadsheet Example:
Office A
Office B
Office C
Office D

I'm looking to make a search that has this as the results:
Category A Category B Category C
Office A 2 0 0
Office B 0 1 0
Office C 0 0 0
Office D 0 1 0

I was thinking of importing the list of Categories and locations as events and going from there, but the real issue here is how do I get Office C to show up as well as Category C in this scenario, when there are no incidents for them.

Here is what I have currently, but it does not show Category C or Office C as they do not exist in the data.
index=myIndex | stats count by Office Category | xyseries Office Category count | fillnull value=0

Tags (1)
0 Karma
1 Solution

aelliott
Motivator

I've figured this out , it was a lot of fun 🙂

|inputlookup Offices.csv | join type=left max=0 [|inputlookup Categories.csv ] | join type=left OFFICE_DESC, Category [search index=incident_logs_dev | stats count by OFFICE_DESC Category] | fillnull value=0 | xyseries OFFICE_DESC Category count

View solution in original post

0 Karma

aelliott
Motivator

I've figured this out , it was a lot of fun 🙂

|inputlookup Offices.csv | join type=left max=0 [|inputlookup Categories.csv ] | join type=left OFFICE_DESC, Category [search index=incident_logs_dev | stats count by OFFICE_DESC Category] | fillnull value=0 | xyseries OFFICE_DESC Category count

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...