Event correlation

[miguel1423]

Hello,

I would like to display some VPN informations in splunk like username, host information, session id. My problem is that I cannot display username and host information in the same table, the user field doesn't exist??

below the search that I did.

I search for user with specifique application use and I try to catch the username, session_id and client_info_host but I get nothing

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" | stats count by user session_id client_info_host | table session_id user client_info_host

If I remove the field "client_info_host" I get the result below. 

index="index_X" partition="/Common/XXXXXXXXX:Common" | stats count by user session_id | table session_id user client_info_host

If I filter only with the field "client_info_host" I don't get the value username on the filed with this event.

But for all events the commune value is the session_id, How can I collerate all fields from session_id ?

Regards,

[gcusello]

Hi @miguel1423,

after a stats command you have only the fields in the stats, in your first case: user, session_id, client_info_host.

In the second and third example you don't use some fields in the BY clause, for this reason you don't have in the following table.

If you want them, you have to put them in the stats command, e.g. using values option, something like this:

index="index_X" partition="/Common/XXXXXXXXX:Common" 
| stats values(client_info_host) AS client_info_host count by user session_id
| table session_id user client_info_host

Ciao.

Giuseppe

[miguel1423]

Hello thank you for your reply,

I tried your suggestion but I get the username and session id but not the client_info host 😕 😭

P.S: The  client_info host data is not in the same event that the username, but the session id is in all events.

Regards,

Miguel

[gcusello]

Hi @miguel1423,

sorry I didn't see that there's a space between "client_info" and "host": are you meaning two fields or one field with space between?

Anyway, the approach is the one I described:

use values(field) AS field for all the fields that you haven't in BY clause and you want in table.

Beware to correctly write the field names (they are case sensistive).

Ciao.

Giuseppe

[ITWhisperer]

It looks like client_info_host hasn't been extracted. Can you find an event with this field? Does it also have the user and session id field?

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" | stats count values(client_info_host) as client_info_host values(user) as user by session_id | table session_id user client_info_host

[miguel1423]

Hello Thank you to answer,

Yes I can find event with this field but the username isn't in the same event. The only same value that I can find in all event is the session id field

Regards,

[ITWhisperer]

So did you try the query I posted?

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" 
| stats count values(client_info_host) as client_info_host values(user) as user by session_id 
| table session_id user client_info_host

This stats should "join" these events by session_id giving you the values of client_info_host and user for each session_id

[miguel1423]

Hello,

Cool, yes it's work thank you 😁 if I want tu show the connexion time Do I need tu insert the field "_time" after the "by session_id" ? and in the table

Regards

[ITWhisperer]

No, but you could add earliest(_time) as session_start

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" 
| stats count values(client_info_host) as client_info_host values(user) as user earliest(_time) as session_start by session_id 
| table session_id user client_info_host session_start

If you do something similar with latest(_time) as session_end you could calculate the duration of the session, if that is what you are after 

[miguel1423]

Hi,

I have a last request, now I'm going to try to get a specific message_id and his value "message"

I added the value in the stats count value as below but I get all message and message ID I can't sort by message id with the field "where" Do you have any idea about that ?

index="index_XX" partition="/Common/XXXXXXXX:Common" 
| stats count values(client_info_host) as client_info_host values(user) as user values(message) as message values(message_id) as message_id  by session_id 
| dedup user
| where message_id="0505" 
| table session_id user client_info_host message_id message

Regards,

Miguel

[ITWhisperer]

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" | stats count values(client_info_host) as client_info_host by user session_id | table session_id user client_info_host