Script - Using NwConsole to hunt query performance with Topquery

Written by Admin | Aug 18, 2017 4:00:00 AM

10.6 introduced a new CLI feature in NwConsole that allowed reporting with topquery.  This neat command allows the parsing of the /var/log/messages file for query commands on brokers, concentrators and archivers to report on query performance.  The is a very helpful command to dig into general analyst performance (best practices, poor syntax, optimization opportunities etc.) and hunt down slowness in the NetWitness Suite.

CLI: Commands used for Troubleshooting 

Command Line Interface for version 10.6.4 

This is a sample line from the output of topquery

  1. # Dec  3 13:43:46 loki NwConcentrator[15854]: [SDK-Values] [audit] User admin (session 54557, 10.105.45.109:49552) has f  
  2. inished values (channel 55739, queued 00:00:00, execute 00:25:13): fieldName=event.cat.name id1=491506493860 id2=7516691  
  3. 19298 threshold=100000 size=20 flags=sessions,sort-total,order-descending,ignore-cache where="time=\"2015-12-03 11:39:00  
  4. \"-\"2015-12-03 17:38:59\""  
  5. /sdk values fieldName=event.cat.name id1=491506493860 id2=751669119298 threshold=100000 size=20 flags=sessions,sort-tota  
  6. l,order-descending,ignore-cache where="time=\"2015-12-03 11:39:00\"-\"2015-12-03 17:38:59\""  

Each query is represented twice (the second starts at /sdk).

  • You can see the user that ran the query 'User admin'.  Depending on where the query was run from this may be the actual user logged in (investigation drills) or it may be the service account that was used to connect the RE service to all the components (and RE rules or ESA rules).
  • You are able to see how long the query ran for (execute) and how long it might have waited to execute (queued)
  • You can see the metakey that was use to drill onto in this case from investigator - navigate it was on event.cat.name
  • the timeframe for the query is also displayed
  • the ordering of the data is also shown (order-descending)
  • the default size of 20 values is also used to load on the drill ( a give away that this is the investigate view and default settings)

There are lots of options to topquery and it can be very handy to run this during monthly reviews of the platform to review query operations and make sure bad habits arent creeping into the analyst workflows.  One to be especially careful of is Investigate - advanced queries where the times are taking longer than normal to execute - check for queries on non index-values keys as this can create a major performance hit on the system.

Here is the help menu output from NwConsole - Topquery

  1. Usage: topQuery [days=#] [hours=#] [time1= ]  
  2.                 [time2= ] [user= ] [top=N]  
  3.                 [match= ] [input= ]  
  4.                 [delimiter=","] [append=<0,1>] [dateRegex= ]  
  5.                 [regex= ] [show] [distribution] [canceled]  
  6. Returns the top N longest running queries from the audit log (either a file or  
  7. from the log API)  
  8.    
  9.     days         - Indicates the time range to query logs.  hours/days is how  
  10.                    far back from NOW.  
  11.     hours        - Indicates the time range to query logs.  hours/days is how  
  12.                    far back from NOW.  
  13.     time1        - Indicates the starting time range to query logs  
  14.     time2        - Indicates the ending time range to query logs  
  15.     user         - The user who submitted the query, by default searches all  
  16.                    users, use (admin|user1|user2) for multiple  
  17.     top          - The top N queries to display, by default shows the longest  
  18.                    executing 100 queries in the time range  
  19.     match        - The types of queries to match, default is:  
  20.                    values,query,timeline  
  21.     input        - Parse a log file for the queries  
  22.     output       - The optional output path where the logs will be saved,  
  23.                    otherwise logs are written to console  
  24.     append       - If 1, will append to existing file, zero overwrites  
  25.                    (default)  
  26.     dateRegex    - If passed in, this will be used to parse the date (syslog  
  27.                    only) instead of the default  
  28.     regex        - If passed in, this will be used to parse the complete log  
  29.                    instead of the default  
  30.     show         - Shows all the regex expressions that are used by default  
  31.     distribution - Groups query execution times into the provided distribution.  
  32.                    Must be a comma separated list of increasing seconds.  
  33.                    Example: distribution=10,20,30,60,300  
  34.     canceled     - If true, it will analyze canceled queries instead of queries  
  35.                    that finished  

Want to see who is downloading pcap files from the decoders and how long it took?

  1. [loki.netwitness.local:50005] /> topquery match=packets top=10  
  2. # 177682836    audit    2016-Feb-19 16:15:20    SDK-Packets    User administrator (session 216639, 10.105.33.127:65256) has finished packets (channel 216675, queued 00:00:00, execute 00:00:01): sessions=26197520000-26197520010 op=start  
  3. /sdk packets sessions=26197520000-26197520010 op=start  
  4.   
  5. # 177682848    audit    2016-Feb-19 16:15:37    SDK-Packets    User administrator (session 216639, 10.105.33.127:65256) has finished packets (channel 216699, queued 00:00:00, execute 00:00:00): sessions=26197520015-26197520020 op=start  
  6. /sdk packets sessions=26197520015-26197520020 op=start  
  7.   
  8. 2 queries were analyzed that match the specified criteria  
  9. 2 (100.0%) queries executed <= 5 seconds  
  10. 0 (0.0%) queries executed <= 10 seconds  
  11. 0 (0.0%) queries executed <= 20 seconds  
  12. 0 (0.0%) queries executed <= 30 seconds  
  13. 0 (0.0%) queries executed <= 60 seconds  
  14. 0 (0.0%) queries executed <= 120 seconds  
  15. 0 (0.0%) queries executed <= 300 seconds  
  16. 0 (0.0%) queries executed <= 600 seconds  
  17. 0 (0.0%) queries executed <= 1200 seconds  
  18. 0 (0.0%) queries executed <= 3600 seconds  
  19. 0 (0.0%) queries executed > 3600 seconds  

Want to change the distribution of the time buckets for the query results to something other than default?

  1. > login loki.netwitness.local:50005 administrator  
  2.    
  3. Password: *******************  
  4. Successfully logged in as session 12102   
View full post