topic Re: How do you Join tables using a common field? in Splunk Search

How do you Join tables using a common field?

umakanth_k — Tue, 29 Sep 2020 22:36:44 GMT

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.

source="dbc:sccm:inv" (run every day) = has the device name, Operating System version and other details
sourcetype=sccm:task_sequence_summary (configured as rising column) = has the Windows 10 deployment summary
sourcetype=sccm:task_sequence_failures (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, ""
        )]

Re: How do you Join tables using a common field?

afurrowgtri — Tue, 08 Jan 2019 17:28:25 GMT

Can you elaborate on your mileage with stats? If you could share a sanitized copy of your output and why it's not working that would be helpful.

The goal with stats is to get all the data for your common field (Hostname) up front and then process it from there. I would try something like:

(source="dbc:sccm:inv" Hostname="*") OR (sourcetype=sccm:task_sequence_summary Hostname="*") OR (sourcetype=sccm:task_sequence_failures Hostname="*" ExitCode="*")
| dedup sourcetype,Hostname sortby -_indextime
| eval FailureCause=case(ExitCode=="10001","Failed due to reason 1",ExitCode=="10002","Failed due to reason 2",True(),"No failure code found")
| eval FailureCause=case(LastStateName=="Failed",FailureCause,True(),NULL())
| eval ExitCode=case(LastStateName=="Failed",ExitCode,True(),NULL())
| stats first(*) AS * by Hostname

If you must use join, you can speed up the subsearch a bit by including only results containing a bad ExitCode.

(sourcetype=sccm:task_sequence_failures Hostname="*" ExitCode="10001" OR ExitCode="10002")

Also consider specifying index=YourIndex in your searches to save Splunk from looking through all indexes for those sourcetypes. If it had a hit in a non-internal index for that source or sourcetype key/value pair it might include it, which is undesirable.

Re: How do you Join tables using a common field?

umakanth_k — Tue, 29 Sep 2020 22:40:27 GMT

To be honest i am not good with stats and i use it only for the basic functions and not for joining tables.
I tried the below, it joins but for some reason it does not provide the right info.

(source="dbc:sccm:inv" Hostname="*") OR (sourcetype=sccm:task_sequence_summary Hostname="*") OR (sourcetype=sccm:task_sequence_failures Hostname="*" ExitCode="*")
 | stats first(*) AS * by Hostname

It could be that the way we are pulling data is wrong hence adds to the complexity.

Like i mentioned :
source="dbc:sccm:inv" = runs every 12 hours and has all devices info
sourcetype=sccm:task_sequence_summary (configured as rising column) = this has only delta
sourcetype=sccm:task_sequence_failures (configured as rising column) = this has only delta

Because of which i want "dbc:sccm:inv" to have 12 hours and the other 2 tables to be searched with "all time"
Would adding earliest=1 in the 2 other searches and pick 12 hours in the time picker for the main search work?

 (source="dbc:sccm:inv" Hostname="*") OR (sourcetype=sccm:task_sequence_summary Hostname="*" earliest=1) OR (sourcetype=sccm:task_sequence_failures Hostname="*" ExitCode="*" earliest=1)
     | stats first(*) AS * by Hostname --- 12 hours in the time picker

In the below scenario "Device 2" will go through multiple states (waiting, failed, successful etc) and i need only the latest one based on the ExecutionTime.
How will "stats" work in this scenario. Which state does Device 2 get?

Also though Device 2 has a failure info in the 3rd table it was successful in the 2nd table hence it should not failure info. I can use this but just asking.
| eval FailureCause=case(LastStateName=="Failed",FailureCause,True(),NULL())
| eval ExitCode=case(LastStateName=="Failed",ExitCode,True(),NULL())

In summary a device needs to have a state based on the execution time (2nd table) and only if it is failed is when it should have the failure details (3rd table) .

Hostname, OS (source="dbc:sccm:inv")
Device 1, Windows 7
Device 2, Windows 10
Device 3, Windows 10

Hostname, LastSateName, ExecutionTime (sourcetype=sccm:task_sequence_summary)
Device 2, Successful, 1 Jan
Device 2, Failed, 20 Dec
Device 2, Running, 13 Dec
Device 2, Waiting, 12 Dec
Device 3, Successful, 12 Dec

Hostname, ExitCode (sourcetype=sccm:task_sequence_failures)
Device 2, 10001

Re: How do you Join tables using a common field?

umakanth_k — Wed, 09 Jan 2019 03:46:47 GMT

Also, regarding the join, the failure table has only failures hence everthing is a bad ExitCode and i cannot filter it based on that.

Re Index, i was told by one of admins that they have only one index for some reason( i might be wrong) hence they dont specify the index at all. I will reconfirm them.