Building a table from a specific field extraction

Path Finder


This is an interesting one. We#'re trying to produce a report from information captured from a network switch, to show what commands a user has entered.

The raw data comes in as syslog, an looks like this:

Apr 29 13:44:32 3979: 004771: Apr 29 13:44:32.422 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:interface GigabitEthernet1/0/1 
Apr 29 13:44:41 3980: 004772: Apr 29 13:44:41.498 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:logging event trunk-status 
Apr 29 13:44:45 3981: 004773: Apr 29 13:44:45.617 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:logging event link-status 
Apr 29 13:44:55 3982: 004774: Apr 29 13:44:55.768 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:interface GigabitEthernet2/0/1 
Apr 29 13:44:56 3983: 004775: Apr 29 13:44:57.185 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:logging event link-status 
Apr 29 13:44:58 3984: 004776: Apr 29 13:44:59.131 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:logging event trunk-status 
Apr 29 13:44:58 3985: 004777: Apr 29 13:44:59.903 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:exit

The bits I'm interested in are 'user' and 'command'.

So, user is automatically extracted (thanks Splunk), and I can grab 'command' with a rex extraction. So my search looks like this:

%PARSER | rex "command:(?<command>[^\$]*)"

So far, so good.

As I want to combine commands entered on specific switches by specific users, I can use the transaction command:

%PARSER | rex "command:(?<command>[^\$]*)" | transaction host user

Again, really good. All the commands above get bundled into one event.

What I now want to do is present the information without all the 'noise'. I.e. I just want a list of the 'commands'. So I'd like to see something like this:

interface GigabitEthernet1/0/1
logging event trunk-status
logging event link-status
interface GigabitEthernet1/0/2
logging event trunk-status
logging event link-status

And this is the bit I'm struggling with.

I've tried this:

%PARSER | rex "command:(?<command>[^\$]*)" | transaction host user | table command

But the result is:

interface GigabitEthernet1/0/1
interface GigabitEthernet1/0/2
logging event trunk-status
logging event link-status

So, maybe I'm not understanding the usage, but my questions are:

  1. Why are some of the 'commands' (i.e. the fields) missing. So, where's the 'exit' command from the original data?
  2. Why are some of the lines missing? So, there should be 2 x 'logging event trunk-status' and 'logging event link-status'.
  3. Is there a way to have the table sorted by event time, rather than alphabetical based on the 'command' field?

Or, is there a better way to achieve what I'm trying to do?

Any help would be appreciated.



Tags (1)

Path Finder

Just an update on this one.

We found that when pasting config into a switch, the _time field wasn't accurate enough to sort the commands in sequence.

See here for an example:

May  5 11:37:42 8362: 011186: May  5 11:37:46.379 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:vlan 4001
May  5 11:37:42 8363: 011187: May  5 11:37:46.622 BST: %PARSER-5-CFGLOG_LOGGEDCMD: User:gmor  logged command:name ISP_Public#4001

_time is 11:37:42 for both events.

So there are two options. Extract either:

  • The 'sequence number' (in this example, they're 011186 and 011178)
  • Extract the timestamp from the syslog message itself

As I'm lazy, I went for the sequence number, with this rex:

rex "\s[\d]*:\s(?<sequence>[\d]*)"

I can now add use this to do:

%PARSER | rex "\s[\d]*:\s(?<sequence>[\d]*):\s" | sort -host, +sequence | table _time host user sequence command

or ultimately add it into my props.conf file.

This now gives me:

_time   host    user    sequence    command 
05/05/2010 11:37:42.000   gmor    011186  vlan 4001
05/05/2010 11:37:42.000   gmor    011187  name ISP_Public#4001

Sorting by the fields 'host' and 'sequence' now gives me the information in the format that I need.

FYI - To get sequence numbers in syslog on a Cisco switch, you need to be using commands like:

service timestamps debug datetime msec localtime show-timezone
service timestamps log datetime msec localtime show-timezone
service sequence-numbers



0 Karma

Path Finder

Hi All,

Thanks to both the suggestions above, I think I've now got what I need. I've gone for:

%PARSER | rex "command:(?<command>[^\$]*)" | table host user command _time | reverse

This give me data that looks like this:

    _time   host    user    command 
1   29/04/2010 13:44:32.000   gmor    interface GigabitEthernet1/0/1
2   29/04/2010 13:44:41.000   gmor    logging event trunk-status
3   29/04/2010 13:44:45.000   gmor    logging event link-status
4   29/04/2010 13:44:55.000   gmor    interface GigabitEthernet2/0/1
5   29/04/2010 13:44:56.000   gmor    logging event link-status
6   29/04/2010 13:44:58.000   gmor    logging event trunk-status
7   29/04/2010 13:44:58.000   gmor    exit

This achieves what I'm looking for in giving me a well formatted table of what commands a specific user did, on what host, when and in sequence.

I'll now move the field extraction into my props.conf, so I'll finally end up with a very clean search of:

%PARSER | table host user command _time | reverse

Thanks all.



How about something like this:

%PARSER | rex "command(?[^\$]*)" | transaction host user | stats count by command,user,host,_time | fields command,host,user,_time | sort -_time

I played around with my syslog information and was able to have a results table with 4 columns that gives me the time the user executed the command on the host. Then sorted by time.

Hope this can help


0 Karma


Looking at it more are you using 4.1 and newer cause I am still using 4.0.8 and do not have the option for the table command.

If I understand what you want is just to see the commands and sort by time. I can do that using | stats count by command | fields command | sort -_time

0 Karma