Splunk Search

Correlate events across Sources

TooManyQuestion
Explorer

Hey all!
  I've seen similar Splunk Help answers similar to mine but I'm having some issues with getting it to work exactly how I want. Essentially I am trying to link together multiple events in one source and then correlate that with another source.

So I have two sources which I've given sample sources of at the bottom of this post.

For each ID listed there is data in TestPOAM.csv and possibly data for it TestRem.csv. For each ID there can be multiple remediation actions listed in TestRem.csv or none at all. My current issue is that when there is no Action Identified in TestRem.csv for an App then I want it to fill with "N/A". Below is my current search which is getting me very close to the results I want.

 

 

index="testdata" (POAMApps="*Test1*" OR RemApps="*Test1*")

| stats
        values("POAMApps") AS "POAMApps"
        values("Description") AS "Description"
        values("ActionID") AS "ActionID"
        values("RemApps") AS "RemApps"
        values("RemAction") AS "RemAction"
        BY "ID"
| sort ID

 

 

The above search gives me the following:
image.png

 

My issue with the above is that when there is nothing identified in ActionID, RemApps, or RemAction I need it to fill that with "N/A".

When I use fillnull like in the following search:

 

 

index="testdata" (POAMApps="*Test1*" OR RemApps="*Test1*")

| fillnull ActionID, RemApps, RemAction value="N/A"

| stats
        values("POAMApps") AS "POAMApps"
        values("Description") AS "Description"
        values("ActionID") AS "ActionID"
        values("RemApps") AS "RemApps"
        values("RemAction") AS "RemAction"
        BY "ID"
| sort ID

 

 

It fills the source data for TestPOAM.csv with N/A meaning that it shows up in columns with actions already in them as pictured in the following:

image.png

 

If someone knows a better way to correlate these events or how to do a fillnull only for one source that help would be greatly appreciated. If anything is confusing please just let me know and I can clarify.

Data Sources

Source1: TestPOAM.csv

IDPOAMAppsDescription
1Test1Description1
2Test2Description2
3Test3Description3
4Test4Description4
5Test5Description5
6Test6Description6
7Test1, Test6Description7
8Test3, Test5Description8
9Test2, Test3Description9
10Test1, Test5Description10
11Test1, Test2, Test3Description11
12Test2, Test3, Test4Description12
13Test4, Test5, Test6Description13
14Test1, Test4, Test6Description14
15Test2, Test3, Test6Description15

 

Source2: TestRem.csv

IDActionIDRemAppsRemAction
11Test1Action1
12Test1Action2
23Test2Action3
24Test2Action4
35Test3Action5
56Test5Action6
67Test6Action7
78Test1, Test6Action8
79Test1Action9
710Test6Action10
811Test3, Test5Action11
1112Test1, Test2, Test3Action12
1113Test1, Test2Action13
1114Test2, Test3Action14
1115Test1Action15
1116Test3Action16
1217Test2, Test3, Test4Action17
1218Test3, Test4Action18
1219Test2Action19
1220Test3Action20
1321Test4, Test5, Test6Action21
1422Test4Action22
1523Test2, Test3, Test6Action23
1524Test2Action24
1525Test3Action25
1526Test6Action26
1527Test2, Test6Action27
1528Test3, Test6Action28
1529Test2, Test3Action29
1530Test2Action30
1531Test6Action31
Labels (3)
0 Karma
1 Solution

TooManyQuestion
Explorer

Managed to get it using a transaction command for anyone who comes across this.

index="testdata" (POAMApps="*Test1*" OR RemApps="*Test1*")
| transaction ID
| fillnull ActionID, RemApps, RemAction value="N/A" 
| stats
        values("POAMApps") AS "POAMApps"
        values("Description") AS "Description"
        values("ActionID") AS "ActionID"
        values("RemApps") AS "RemApps"
        values("RemAction") AS "RemAction"
        BY "ID"
| sort ID

View solution in original post

0 Karma

TooManyQuestion
Explorer

Managed to get it using a transaction command for anyone who comes across this.

index="testdata" (POAMApps="*Test1*" OR RemApps="*Test1*")
| transaction ID
| fillnull ActionID, RemApps, RemAction value="N/A" 
| stats
        values("POAMApps") AS "POAMApps"
        values("Description") AS "Description"
        values("ActionID") AS "ActionID"
        values("RemApps") AS "RemApps"
        values("RemAction") AS "RemAction"
        BY "ID"
| sort ID
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 ...