Join Indexes and Host to Build one Query

[GRC]

Hello champions,

I run the below 1,2,3 queries on the given datasets to find out which users ran the enable command on which host at what time:

1. index= networking user* enable* host*

Oct 15 08:17:45 brg-c-1.com.au 8279: Oct 15 2021 08:17:44.820 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:John logged command:!exec: enable
Oct 15 08:17:35 brg-c-1.com.au 8278: Oct 15 2021 08:17:34.082 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:lili logged command:!exec: enable failed
Sep 15 23:29:55 gsw-r-4.com.au 466: Sep 15 23:29:54.009: %PARSER-5-CFGLOG_LOGGEDCMD: User:Khan logged command:!exec: enable
Aug 12 15:18:37 edc-r-4.com.au 02: Aug 12 15:18:36.472: %PARSER-5-CFGLOG_LOGGEDCMD: User:Khan logged command:!exec: enable
Aug 11 03:31:05 ctc-s.com.au 134: Aug 10 17:31:04.859: %PARSER-5-CFGLOG_LOGGEDCMD: User:cijs logged command:!exec: enable
Jan 29 11:30:58 brg-c-1.com.au 2082: Jan 29 2021 11:30:57.141 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:chick logged command:!exec: enable failed

2. index=linux_logs host=edc-03-tacacs enable*

Oct 26 12:56:13 egc-03-ts tc_plus[149]: enable query for 'kim' tty86 from 202.168.5.22 accepted
Oct 26 11:33:44 egc-03-ts tc_plus[259]: enable query for 'kim' tty86 from 202.168.5.22 accepted
Oct 21 11:35:59 egc-03-ts tc_plus[285]: enable query for 'John' tty86 from 202.168.5.23 accepted
Oct 21 11:35:53 egc-03-ts tc_plus[282]: enable query for 'Han' tty86 from 202.168.5.23 rejected

3. index=linux_logs host=gsw-03-tacacs enable*

Sep 30 13:35:53 gdw-02-ts tc_plus[143]: 192.168.2.21 James tty1 192.168.6.56 stop task_id=55161 timezone=AEST service=shell start_time=1632972953 priv-lvl=0 cmd=enable
Sep 29 12:38:17 gdw-02-ts tc_plus[319]: 192.168.2.24 linda tty1 192.168.5.3 stop task_id=15729 timezone=AEST service=shell start_time=1632883097 priv-lvl=0 cmd=enable
Sep 15 22:23:23 gdw-02-ts tc_plus[1649]: 192.168.4.2 Brown tty322 192.168.46.1 stop task_id=2574 timezone=AEST service=shell start_time=1631708603 priv-lvl=0 cmd=enable
Sep 9 14:58:32 gdw-02-ts tc_plus[2030]: 192.168.2.29 Gordan tty1 192.168.26.3 stop task_id=14329 timezone=AEST service=shell start_time=1631163512 priv-lvl=0 cmd=enable

I tried hard but could not find a query to merge all these data (indexes and hosts) to find out who ran enable command successfully at what time on which host. And get those results to a table look like

|table date host user command(enable) status(success)

Could anyone please help me ?

Thank you in advance.

[PickleRick]

What do you mean by "join"? These seems to be queries from different devices and such so there's no point in correlating them. Just append the searches (probably renaming some fields if you want to have consistent naming) and that's all.

[GRC]

Hi @PickleRick,

Thank you for your reply. I tried to capture the server names from below data: 

Oct 15 08:17:45 brg-c-1.com.au 8279: Oct 15 2021 08:17:44.820 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:John logged command:!exec: enable
Oct 15 08:17:35 brg-c-1.com.au 8278: Oct 15 2021 08:17:34.082 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:lili logged command:!exec: enable failed
Aug 11 03:31:05 ctc-s.com.au 134: Aug 10 17:31:04.859: %PARSER-5-CFGLOG_LOGGEDCMD: User:cijs logged command:!exec: enable

with the following rex command, but it won't work, can you please help me to see what is wrong ?

| rex field=_raw "\/(?<hostname>[^_\/]+)[\w\.]+$"

Thank you

[PickleRick]

OK, first things first - if you onboard some data, you should make sure that the event stream you're receiving is properly described with metadata (in particular - has proper time and sourcetype). And that that sourcetype has proper configuration for parsing it.

So if you're adding sources to your splunk installation, verify that they parse properly first, then try to manipulate the events.

If you search in verbose mode do you get any reasonable fields in such events or just a default set?

Furthermore - where did you get this regex? You don't have a single slash character in your events so how would you like to match something that is directly following a slash?

[GRC]

@PickleRick 

Actually, what I am doing is generating compliance reports from already onboarded data for GRC purpose. 

The data is already there, the data resides in multiple indexes in different formats. I have no control over the way it was extracted. 

Now I am trying to extract just the server name so that I can keep building the query. If you can show me a way to extract the server name using any other method (not necessarily regex) that would be grate ?

Thank you.

[PickleRick]

Ok. It's kinda strange though that relevant fields aren't extracted already. Then you'd need only to rename them and it would be much easier. Anyway.

If you have to parse the hostname from those events on your own it's getting a bit more messy but it's certainly achievable.

I have to say two things first though:

1) I see that the message format for the first example is inconsistent - some timestamps do have timezone information, some don't.

2) Are you sure that the commands containing "!exec: enable failed" are really what you want?

OK, let's get to business.

For
Oct 15 08:17:45 brg-c-1.com.au 8279: Oct 15 2021 08:17:44.820 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:John logged command:!exec: enable

You'd need something like

| rex "(?:\w{3}\s\d{2}:\d{2}:\d{2})\s(?<hostname>\S+)\s\d+:\s(?<eventtime>\w{3}\s\d{4}\s\d{2}:\d{2}:\d{2}\.\d+(\s\w+)):\s%PARSER5-CFGLOG_LOGGEDCMD:\sUser:(?<user>\w+)\slogged command:!exec:\s(?<command>.*)"

 That's for this precise format. You'll probably need to use strptime against eventtime to get a proper datetime value.

For the second one it'd be

| rex "(?<eventtime>\w{3}\s+\d+\s+\d{2}:\d{2}:\d{2})\s(?<hostname>\S+)\[\d+\]:\senable\squery\sfor\s'(?<user>\w+)'"

Again, some strptime on eventtime will be needed to get the proper time value, not a string.

And for the third one:

| rex "(?:\w{3}\s+\d+\s+\d{2}:\d{2}:\d{2})\s(?<hostname>\S+)\[\d+\]:\s\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\s(?<user>\w+).*start_time=(?<eventtime>\d+)"

Here you don't need to strptime the eventtime because it already seems to be a unix timestamp.

So you do your search1 | rex1 | eval eventtime=strptime() | table eventtime hostname user | append [ search search2 | rex2 | eval eventtime=srptime() | table eventtime hostname user ] | append [ search search 3 | rex3 | table eventtime hostname user ]

And you're good to go.

You just need to find the proper format for strptime (I hate this function ;-)).

[tread_splunk]

| rex field=_raw "^(?:[^ \n]* ){3}(?P<hostname>\S+)"

My regex isn't the best so I accepted this challenge as a way to test myself NOT to spread regex best practice.  So use with caution.  And comments / improvements from the community welcome!  Having said all that, this regex extracts the hostnames.

[GRC]

@tread_splunk Thank you for the trying to help me. Actually it captured the dates in some results. 

I found the working query: 

| rex field=_raw ":\d+ (?<hostname>.+) \d+: \w+"

[tread_splunk]

I should have said, I'm assuming the hostname is the string after the date.  But as per the entries above, you should have some interesting fields already extracted, including host.