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:
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:
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
ID | POAMApps | Description |
1 | Test1 | Description1 |
2 | Test2 | Description2 |
3 | Test3 | Description3 |
4 | Test4 | Description4 |
5 | Test5 | Description5 |
6 | Test6 | Description6 |
7 | Test1, Test6 | Description7 |
8 | Test3, Test5 | Description8 |
9 | Test2, Test3 | Description9 |
10 | Test1, Test5 | Description10 |
11 | Test1, Test2, Test3 | Description11 |
12 | Test2, Test3, Test4 | Description12 |
13 | Test4, Test5, Test6 | Description13 |
14 | Test1, Test4, Test6 | Description14 |
15 | Test2, Test3, Test6 | Description15 |
Source2: TestRem.csv
ID | ActionID | RemApps | RemAction |
1 | 1 | Test1 | Action1 |
1 | 2 | Test1 | Action2 |
2 | 3 | Test2 | Action3 |
2 | 4 | Test2 | Action4 |
3 | 5 | Test3 | Action5 |
5 | 6 | Test5 | Action6 |
6 | 7 | Test6 | Action7 |
7 | 8 | Test1, Test6 | Action8 |
7 | 9 | Test1 | Action9 |
7 | 10 | Test6 | Action10 |
8 | 11 | Test3, Test5 | Action11 |
11 | 12 | Test1, Test2, Test3 | Action12 |
11 | 13 | Test1, Test2 | Action13 |
11 | 14 | Test2, Test3 | Action14 |
11 | 15 | Test1 | Action15 |
11 | 16 | Test3 | Action16 |
12 | 17 | Test2, Test3, Test4 | Action17 |
12 | 18 | Test3, Test4 | Action18 |
12 | 19 | Test2 | Action19 |
12 | 20 | Test3 | Action20 |
13 | 21 | Test4, Test5, Test6 | Action21 |
14 | 22 | Test4 | Action22 |
15 | 23 | Test2, Test3, Test6 | Action23 |
15 | 24 | Test2 | Action24 |
15 | 25 | Test3 | Action25 |
15 | 26 | Test6 | Action26 |
15 | 27 | Test2, Test6 | Action27 |
15 | 28 | Test3, Test6 | Action28 |
15 | 29 | Test2, Test3 | Action29 |
15 | 30 | Test2 | Action30 |
15 | 31 | Test6 | Action31 |
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
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