Hi all
I am very new to Splunk, hoping someone can help me.
I am working on creating a dashboard that gives us a summary of our Windows 10 upgrades.
<i>source="dbc:sccm:inv"</i> (run every day) = has the device name, Operating System version and other details
<i>sourcetype=sccm:task_sequence_summary</i> (configured as rising column) = has the Windows 10 deployment summary
<i>sourcetype=sccm:task_sequence_failures</i> (configured as rising column) = this has the failure information which includes why it failed, which step and the exit codes.
In source="dbc:sccm:inv ", there would be Windows 10 and other Operating system devices as well.
I am trying to combine the fields from all the above sources/sourcetypes so that if it is a Windows 10 device, other fields are populated if not empty.
e.g
Hostname, OS (source="dbc:sccm:inv")
Device 1, Windows 7
Device 2, Windows 10
Device 3, Windows 10
Hostname, LastSateName (sourcetype=sccm:task_sequence_summary)
Device 2, Failed
Device 3, Successful
Hostname, ExitCode (sourcetype=sccm:task_sequence_failures)
Device 2, 10001
I want the below :
Hostname, OS, LastStateName, ExitCode
Device 1, Windows 7
Device 2, Windows 10, Failed, 10001
Device 3, Windows 10, Successful
I want to join the 3rd table (i.e sourcetype=sccm:task_sequence_failures) only if device failed to upgrade.
I am doing something like below. It works but is very slow because of the left join (the nested left join reduced the exectution a bit)
I was adviced not to use joins as they are slow and "stats" would be a better option.
I tried using a bit of "stats" but couldnt achieve much.
source="dbc:sccm:inv"
| dedup Hostname sortby -_time
| join type=left Hostname
[| search sourcetype=sccm:task_sequence_summary earliest=1
| dedup Hostname sortby -LastStatusTime
| join type=left Hostname
[| search sourcetype=sccm:task_sequence_failures earliest=1
| dedup Hostname sortby -ExecutionTime
| eval FailureCause=
case(
ExitCode = "10001", "Failed due to reason 1",
ExitCode = "10002", "Failed due to reason 2",
1=1,"Unknown"
)
]
| eval FailureCause=
case (
LastStateName = "Failed", FailureCause,
1=1, ""
)
| eval ExitCode=
case (
LastStateName = "Failed", ExitCode,
1=1, ""
)]
... View more