topic Re: Stats table manipulation in Splunk Search

Stats table manipulation

_smp_ — Fri, 15 Sep 2017 20:07:34 GMT

I created the following search to audit the changes made to our network infrastructure:
(index=ise Protocol=Tacacs MESSAGE_CODE=5202) OR (index=acs process="Tacacs-Accounting" MESSAGE_CODE=3300)
| rex field=CmdSet mode=sed "s/^\[(?: )?|CmdAV= ?\]?|CmdArgAV=(?:)?|(?:)?\s\]//g"
| where CmdSet!=""
| lookup dnslookup clientip AS Address OUTPUT clienthost AS Device
| eval Device=(if(isnull(Device),Address,Device)), Time=strftime(_time,"%H:%M:%S")
| eval Date=strftime(_time, "%m")."-".date_mday."-".date_year
| stats list(CmdSet) AS Command, list(Time) AS Time BY Date,User,Device

Here's some sample output:

Date          User     Device           Command                                               Time
09-14-2017  admin   access-switch    switchport access vlan 600                            13:13:32
                                        interface GigabitEthernet 1/0/26                      13:13:25
                                        no shutdown                                           13:13:57
                                        shutdown                                              13:13:56
09-14-2017  admin   core-router      transfer upload start                                 17:36:08
                                        transfer upload password <hidden>                     17:36:08
                                        transfer upload username transfer                     17:36:08
                                        transfer upload filename core-router-confg            17:36:07
                                        transfer upload serverip 10.10.10.1                   17:36:07
                                        transfer upload datatype config                       17:36:07
                                        transfer upload port 21                               17:36:06
                                        transfer upload mode ftp                              17:36:06

There's a couple of issues I'm really struggling with:
1. I would like to eliminate rows /AFTER/ the stats command where the Command starts with 'transfer upload' or any number of other command snippets. I have spent the day trying various techniques like |where but I can't seem to figure how eliminate these rows. I realize I can do this with a regex before the stats, but I'm trying to learn some more advanced techniques.
2. I can't figure out how to sort the rows by Time. When I use the sort command, I lose all of the grouping and it becomes table output. Is there a way to sort the Commands in the stats output based on the Time column (also preserving the value in the Time column)?
3. There are some rows where the list() limit of 100 is a factor. Is there a better way to construct this search to work around that limit (as opposed to increasing the limit)? I tried using values(), but I seem to loose the relationship between the Command and Time fields.

Really struggling here, thanks.

Re: Stats table manipulation

naidusadanala — Fri, 15 Sep 2017 20:20:59 GMT

--> To eliminate transfer upload from stats result

Instead of where replace it with search CmdSet!="" AND Cmdset!=transfer*

For the list You need to bump the size in limits.conf

Re: Stats table manipulation

cmerriman — Fri, 15 Sep 2017 20:24:25 GMT

for your first request, after reading it a few times, are you looking to remove any Command that contains "transfer upload"? if so, you could try (index=ise Protocol=Tacacs MESSAGE_CODE=5202) OR (index=acs process="Tacacs-Accounting" MESSAGE_CODE=3300) | rex field=CmdSet mode=sed "s/^\[(?: )?|CmdAV= ?\]?|CmdArgAV=(?:)?|(?:)?\s\]//g" | where CmdSet!="" |search CmdSet!="transfer upload*"|...

Re: Stats table manipulation

_smp_ — Fri, 15 Sep 2017 20:27:07 GMT

Thanks, but is there a way to do this /after/ the stats command? I'm trying to get more sophisticated with the language.

Re: Stats table manipulation

_smp_ — Fri, 15 Sep 2017 20:31:59 GMT

I updated my description of #1. I didn't even think to use this technique (duh!) so thanks for the post. I am wondering if there's a way to remove certain rows after the stats() command.

Re: Stats table manipulation

naidusadanala — Fri, 15 Sep 2017 20:34:06 GMT

Place |search Cmdset!=transfer* at the end of the search

Re: Stats table manipulation

DalJeanis — Fri, 15 Sep 2017 21:38:08 GMT

Here's some stuff to play with...

(index=ise Protocol=Tacacs MESSAGE_CODE=5202) OR (index=acs process="Tacacs-Accounting" MESSAGE_CODE=3300)
| rex field=CmdSet mode=sed "s/^\[(?: )?|CmdAV= ?\]?|CmdArgAV=(?:)?|(?:)?\s\]//g"
| where CmdSet!=""
| lookup dnslookup clientip AS Address OUTPUT clienthost AS Device
| eval Device=(if(isnull(Device),Address,Device)), Time=strftime(_time,"%H:%M:%S")
| eval Date=strftime(_time,"%Y-%m-%d")
| eval Command=Time." ".CmdSet
| stats values(Command) AS Command BY Date,User,Device
| eval Command=mvfilter(NOT match(Command,"transfer"))

NOTE - You will save yourself loads of headaches if you just get yourself and your users accustomed to seeing dates in an order where they sort correctly, like "2017-09-11". That way you can compare directly and also read directly without conversion.

And some more stuff to play with...

(index=ise Protocol=Tacacs MESSAGE_CODE=5202) OR (index=acs process="Tacacs-Accounting" MESSAGE_CODE=3300)
| rex field=CmdSet mode=sed "s/^\[(?: )?|CmdAV= ?\]?|CmdArgAV=(?:)?|(?:)?\s\]//g"
| where CmdSet!=""
| lookup dnslookup clientip AS Address OUTPUT clienthost AS Device
| eval Device=(if(isnull(Device),Address,Device)), Time=strftime(_time,"%H:%M:%S")
| eval Date=strftime(_time,"%Y-%m-%d")
| stats list(CmdSet) AS Command, list(Time) AS Time BY Date,User,Device


| rename COMMENT as "Pretending for the moment that you had a report as above, but needed to break it up."
| eval TimeCommand=mvzip(Time,Command,"->")
| fields - Command Time
| mvexpand TimeCommand
| rex field=TimeCommand "(?<Time>[^-]+)->(?<Command>.*)"
| search NOT match(Command,"transfer") 
| stats list(Command) AS Command, list(Time) AS Time BY Date,User,Device

| rename COMMENT as "Or another way, if you are okay leaving them together afterwards."
| eval TimeCommand=mvzip(Time,Command,"->")
| fields - Command Time
| mvexpand TimeCommand
| rex field=TimeCommand "(?<Time>[^-]+)->(?<Command>.*)"
| search NOT match(Command,"transfer") 
| rex mode=sed field=TimeCommand "s/->/  /g"
| mvcombine TimeCommand

updated to close quotes on two rexes.

Re: Stats table manipulation

_smp_ — Tue, 19 Sep 2017 19:30:08 GMT

Very useful reply, thanks. You obviously pointed out a lot of techniques I never even thought about. You also caused me to study the where command enough to realize I wasn't using it correctly. After I figured that out, I was able to use it to eliminate the commands. Unfortunately it didn't appear that your 'break it up' example correctly sorted by the Time column. But using your technique of prepending the 'Time' string to the 'Command' field had the effect I was looking for. I would still like to understand how to sort by Time in a separate column but this is good enough. Here's the search I landed on:

(index=ise Protocol=Tacacs MESSAGE_CODE=5202) OR (index=acs process="Tacacs-Accounting" MESSAGE_CODE=3300)
| rex field=CmdSet mode=sed "s/^\[(?: )?|CmdAV= ?\]?|CmdArgAV=(?:<cr>)?|(?:<cr>)?\s\]//g"
| where CmdSet!=""
| lookup dnslookup clientip AS Address OUTPUT clienthost AS Device
| eval Device=(if(isnull(Device),Address,Device)), Time=strftime(_time,"%H:%M:%S"), Date=strftime(_time,"%Y-%m-%d")
| where NOT (match(User, "admin1") AND (match(CmdSet, "configure terminal") OR match(CmdSet, "transfer upload")))
| where NOT (match(User, "admin2") AND (match(CmdSet, "switchto (\\\;|vdc)") OR match(CmdSet, "copy.*tftp")))
| eval Command="(".Time.")> ".CmdSet
| stats values(Command) AS Command BY Date,User,Device

Thanks for taking the time to teach me something - this was a really useful exercise!

Re: Stats table manipulation

DalJeanis — Tue, 19 Sep 2017 21:36:12 GMT

@scottprigge - YW. It seemed like you were most interested in learning, rather than solving the particular issue, so I figured the demos were more in keeping with the need.

Add line 15.5 to "break it up" demo

| sort 0  Date User Device Time

Where evaluates both sides of the equation, search assumes the right side is a literal

Oh, here, these two are equivalent...

 | eval Device=(if(isnull(Device),Address,Device))

and

 | eval Device=coalesce(Device,Address)

Re: Stats table manipulation

_smp_ — Wed, 20 Sep 2017 14:30:14 GMT

You are right - this is also a bit of a learning exercise, trying to become more advanced with my searches. Thanks for taking a helpful approach.

Are you sure the search NOT match(Command,"transfer") syntax works like that? When I try it against a Command or even a value for the User field, all the results are gone. The help for the search command makes it look like it needs to be more like search NOT Command!="transfer *".

Re: Stats table manipulation

DalJeanis — Wed, 20 Sep 2017 18:28:07 GMT

Looks like my posted code was missing an end quote to the prior rex. Did you catch that? If not, pick it up and try again.

The match looks okay to me. Default is unanchored, meaning if it matches any subset of the field, it matches. Syntax is regex, so your example would require there to be all spaces after transfer. You could do "^transfer" or "^transfer.*" if you wanted, both of which would be equivalent. Probably the first of those two would be the most efficient for your particular messages, but I didn't know what else you might need, and figured you sounded able to work it out. Try something, test, believe the results, but keep a grain of salt handy.

http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/ConditionalFunctions

Re: Stats table manipulation

_smp_ — Wed, 20 Sep 2017 19:07:32 GMT

Yes I caught it, didn't want to be nit-picky 🙂 You know, don't bite the hand that feeds you...

Still toying with match() in a |search NOT command. The documentation you referred me to says it applies to eval,fieldsearch and where, but not search. I have found in my testing that when I use it in search all my results are gone as though it's being treated as a literal string. That's what I suspect, but I haven't convinced myself yet.

Re: Stats table manipulation

woodcock — Sat, 25 Nov 2017 17:23:09 GMT

So we can all play along:

| makeresults
| eval raw="_time=\"09-14-2017,13:13:32\",User=admin,Device=access-switch,CmdSet=\"switchport_access_vlan_600\" _time=\"09-14-2017,13:13:25\",User=admin,Device=access-switch,CmdSet=\"interface_GigabitEthernet_1/0/26\" _time=\"09-14-2017,13:13:57\",User=admin,Device=access-switch,CmdSet=\"no_shutdown\" _time=\"09-14-2017,13:13:56\",User=admin,Device=access-switch,CmdSet=\"shutdown\" _time=\"09-14-2017,17:36:08\",User=admin,Device=core-router,CmdSet=\"transfer_upload_start\" _time=\"09-14-2017,17:36:08\",User=admin,Device=core-router,CmdSet=\"transfer_upload_password_<hidden>\" _time=\"09-14-2017,17:36:08\",User=admin,Device=core-router,CmdSet=\"transfer_upload_Username_transfer\" _time=\"09-14-2017,17:36:07\",User=admin,Device=core-router,CmdSet=\"transfer_upload_filename_core-router-confg\" _time=\"09-14-2017,17:36:07\",User=admin,Device=core-router,CmdSet=\"transfer_upload_serverip_10.10.10.1\" _time=\"09-14-2017,17:36:07\",User=admin,Device=core-router,CmdSet=\"transfer_upload_datatype_config\" _time=\"09-14-2017,17:36:06\",User=admin,Device=core-router,CmdSet=\"transfer_upload_port_21\" _time=\"09-14-2017,17:36:06\",User=admin,Device=core-router,CmdSet=\"transfer_upload_mode_ftp\""
| makemv raw
| mvexpand raw
| rename raw AS _raw
| rex mode=sed "s/,/ /g"
| kv
| eval _time=strptime(time, "%m-%d-%Y %H:%M:%S")
| fields - time
| rex field=CmdSet mode=sed "s/_/ /g"
| rename COMMENT AS "Everything above generates sample event data; everything below is the solution"

You need to sort your events before you stats them so this solves question #2:

| sort 0 _time

This part is OK with a bit of adjustment:

| eval Device=(if(isnull(Device),Address,Device))
| eval Time=strftime(_time,"%H:%M:%S")
| eval Date=strftime(_time, "%m-%d-%Y")
| stats list(CmdSet) AS Command, list(Time) AS Time earliest(CmdSet) AS firstCommand BY Date,User,Device

As far as #1 and "eliminate rows /AFTER/ the stats command where the Command starts with 'transfer upload' or any number of other command snippets", it depends on what you mean by "rows" and "starts with". I will assume you really mean rows, not lines in the "list" fields and that you mean "the first command in time-sequence". If so, you can do something like this:

| where NOT match(firstCommand, "(?:^transfer)|(?:foo$)|bar")

If you mean "remove some of the commands in the list but keep the row", then you can do something like this:

| eval Command=mvfilter(NOT match(firstCommand, "(?:^transfer)|(?:foo$)|bar"))
| where mvcount(Command)>0

Regarding #3, modifying the limit is not that great an idea but if you keep in the order of magnitude (<1000), that should be OK. The only other option would be to use streamstats to mark batches of commands (first 100 get groupOfHundreds="1-100", the next 100 get groupOfHundreds="101-200" and then do 2 stats in a row, one to roll up the groups of 100s and then do a nomv and a second stats to list those.