Getting Data In

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

np75014
Explorer

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

Miles333
New Member

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

0 Karma

lguinn2
Legend

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.

0 Karma

np75014
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...