topic Re: How do I do both filtering and grouping on a timechart? in Getting Data In

How do I do both filtering and grouping on a timechart?

np75014 — Fri, 16 Jan 2015 10:10:48 GMT

My Splunk server is being forwarded events from a remote Windows machine. Those events correspond to device connections and disconnections. Each event corresponds to either a connection or disconnection.

I have a CSV file containing information about those devices. The CSV file's headers looks like this:
DriverName,DeviceManufacturer,DriverVersion,DeviceModel,DeviceCategory

Only DeviceManufacturer and DeviceModel information are available in the forwarded events. The others are not.

This is what I need to do:
** 1.** Display on a timechart the connected devices for every hour during the last day.
2. Filter the displayed data by one or more of the fields of the CSV file that are listed above.
3. Provide the options to group the displayed data by one of the same fields of the CSV file that are listed above.

Example:
List the connected devices every hour during the last day filtered by DeviceManufacturer=X and DeviceModel=Y, grouped by DeviceCategory (See example).

For now I managed to do points 1 and 2, but I cannot combine them with point 3. (I can display the timechart and filter by fields but cannot combine them with the grouping).
I did this using the following search:

eventtype=DRIVER_STATUS_CHANGED | rex "Message=(?.+?) status changed from (?.+?) to (?.+?)\." 
| eval counter=case(to_status="Connected", 1, from_status="Connected", -1, 1==1, 0) 
| timechart span=1m sum(counter) as counter 
| streamstats sum(counter) as counter | eventstats min(counter) as min 
| eval counter=if(min < 0, counter + (min*-1), counter) | fields - min

Re: How do I do both filtering and grouping on a timechart?

lguinn2 — Fri, 16 Jan 2015 18:53:01 GMT

Sorry, I could not see your image at http://www.filedropper.com/image_5

And something is definitely wrong with the rex command in the your question - it isn't extracting any fields. So I think there may be a cut-and-paste problem there... Try this:

eventtype=DRIVER_STATUS_CHANGED DeviceManufacturer=X and DeviceModel=Y
| rex "Message=(?.+?) status changed from (?<from_status>?.+?) to (?<to_status>?.+?)\." 
| lookup device_lookup DeviceManufacturer DeviceModel
| eval counter=case(to_status="Connected", 1, from_status="Connected", -1, 1==1, 0) 
| bucket span=1m _time
| stats sum(counter) as counter by DeviceCategory _time
| eventstats min(counter) as min by DeviceCategory
| eval counter=if(min < 0, counter + (min*-1), counter) | fields - min
| xyseries _time DeviceCategory counter

You can find more information about Lookups here.

Re: How do I do both filtering and grouping on a timechart?

np75014 — Sun, 18 Jan 2015 17:03:27 GMT

Thank you for your answer.

It helped me a lot but I still have some questions in order to really acheive my goal:
- How can I represent these search in a timechart for the last 24 hours or more?
- How can I use the DeviceCategory field as a parameter of the search that can be changed by the user? For example, if i want to group the events by DeviceManufacturer or Drivername or Just None instead?

Re: How do I do both filtering and grouping on a timechart?

np75014 — Tue, 20 Jan 2015 09:04:56 GMT

https://www.dropbox.com/sh/voxr1p5uj6o1mgi/AAAeiYOLQUU8jtFqVTU8-xsBa?dl=0#lh:null-deviceutilizationexample.png

Re: How do I do both filtering and grouping on a timechart?

Miles333 — Wed, 05 Oct 2016 11:13:05 GMT

I still have several points to be clarified. First of all, how can this search be represented in a timechart? And I also want to group events by Drivername or None. I guess something's wrong in a command I'm trying. I'll try to check it thoroughly.
Miles - Check http://domyhomeworkonline.net/ with Miles Preskott topic