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.
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.
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.
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!
@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)
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 *"
.
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
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.
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*"|...
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.
--> 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
Thanks, but is there a way to do this /after/ the stats command? I'm trying to get more sophisticated with the language.
Place |search Cmdset!=transfer* at the end of the search