Splunk Search

Stats table manipulation

_smp_
Builder

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.

Tags (2)
0 Karma

woodcock
Esteemed Legend

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

_smp_
Builder

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!

DalJeanis
SplunkTrust
SplunkTrust

@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)

_smp_
Builder

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 *".

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

_smp_
Builder

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.

0 Karma

cmerriman
Super Champion

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*"|...

0 Karma

_smp_
Builder

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.

0 Karma

naidusadanala
Communicator

--> 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

0 Karma

_smp_
Builder

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

0 Karma

naidusadanala
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...