How to display fields from two queries only if timestamps match?

[klay824]

Hi,

I have a query that is making two different searches and displaying the stats of each. Example:

index="example" TERM(STOP)

    | rename message.payload as message1
    | stats count by message1

    | appendcols [search index="example2"
        | rename message.payload as message2

        | stats count by message2]

I want the results of message1 and message2 whose event timestamps are identical to be displayed next to each other in statistics tab. I would like to have the stats displayed as such:

Message1         Message2        Count

<data>                 <data>               23

<data>                 <data>               17

Is this possible?  I hope this makes sense, I am still somewhat new to writing Splunk queries and this is so far the most complex one I have needed to write.

[Tom_Lundie]

Does this do what you need it to?

(index="example" TERM(STOP)) OR index="example2"
| eval message1=if(index=example, message.payload, null())
| eval message2=if(index=example2, message.payload, null())
| stats values(message1) as message1, values(message2) as message2, count by _time

[klay824]

Thanks for your reply! I think I may have written my example queries poorly.  The indexes are the same, but each have additional parameters.

First is:

index="example" TERM(STOP)

Second is:

index="example" TERM(index)

If my indexes have the same name, how can I modify your example?

[Tom_Lundie]

Ah-ha, I understand.

Depending on exactly what you're trying to acheive.

If you truly are looking to use TERM in the conventional sense to group the message.payload properties, then you can let Splunk natively take care of the different TERMs via a subsearch:

index="example" TERM(STOP)
| eval message1 = src_ip
| table _time, message1 
| append 
    [| search index="example" TERM(index) 
    | eval message2 = src_ip 
    | table _time, message2
        ] 
| stats values(message1) as message1, values(message2) as message2, count by _time

If you know that your TERMs will always have a consistent prefix/suffix, e.g. always surrounded by a space, then you could use the LIKE command as follows:

index="example" (TERM(STOP) OR TERM(index))
| eval message1=if(LIKE(_raw,% STOP %), message.payload, null())
| eval message1=if(LIKE(_raw,% index %), message.payload, null())
| stats values(message1) as message1, values(message2) as message2, count by _time

[klay824]

Hi Tom, 

I have not been able to get your suggestion to show all of the data. It will either display the value of message1 OR message2 in the stats tab.  I think this is because the payload location for both of these is the same JSON path?  message.payload for each of them. Could they be overwriting each other?

I think I have been able to use a subsearch of index="example" TERM(Stop) and use the timestamp of those events to run the outer search of index="example" message.payload.log="controller"

The query looks like this:

index="example" message.payload.log="controller"
| rename message.payload AS message1

[search index="example" TERM(STOP)
   | rename message.payload AS message2
   | table _time]
| stats count by message1

This works to filter the outer search ("controller") by the timestamp of the inner search (TERM(STOP)), but I would love to be able to show the data from message two in the stats page, next to the results from message1. So far, I am not able to get both message1 and message2 to show up, as they seem to override one.

[Tom_Lundie]

Hi @klay824,

Your example looks good but going back to my previous post, the message.payload's won't be overriding each other because they are in totally independent searches. With this search, if message1 and message2 occurred at the exact same _time (down to the millisecond) then they will be grouped together. Try this search, which uses a where at the end to filter down to where message1 and message2 are populated at the same _time:

index="example" TERM(STOP)
| rename message.payload as message1
| table _time, message1 
| append 
    [| search index="example" TERM(index) 
    | rename message.payload as message2
    | table _time, message2
        ] 
| stats values(message1) as message1, values(message2) as message2, count by _time
| where isnotnull(message1) AND isnotnull(message2)

[klay824]

Thank you Tom! I will give this a try today.