Splunk and Casper

[solomonacquah]

Using Splunk with Casper

Casper reports are great, but I soon realized are too much work to run, open in excel and make pretty graphs that management could understand. With that said I decided to implement splunk. I am by no means a splunk expert but with basic knowledge it was quite simple.

1. First off splunk is free, download it. http://www.splunk.com/download. My company was already utilizing splunk but I decided to implement my own server for testing reasons.

2. After splunk was implemented I read up a little and found that splunk has a mysql connector which is great since Casper uses MYSQL. Since I wanted splink to talk to Casper I would need to install the splunk mysqlconnector, which can be found here http://splunk-base.splunk.com/apps/36664/splunk-mysql-connector

3. Now for the Casper part, since you will reading the raw JSS MYSQL database, you will need to first create a read only mysql account so that it can access to the JSS database. You can run a google search to figure that out.

4. I used Mysql workbench to test my queries, but you can use any visual mysql query tool you like.

5. After I confirmed my mysql read-only account worked and was successfully pulling data from the JSS database with my queries I went back to splunk.

For my situation I already had smart groups that were giving me the data, and the data that management wanted, so all I had to do is query mysql for the smart groups I had already created and then tell splunk how and where to display the data. Below is the splunk search I used via the splunk mysql connector.

| mysqlquery spec=Casper_Splunk query="select cg.computer_group_name, count(c.computer_id)
from computer_groups cg
inner join computer_group_memberships cgm
    on cg.computer_group_id = cgm.computer_group_id
inner join computers c
    on cgm.computer_id = c.computer_id
where cg.is_smart_group = 1
 and computer_group_name = 'Encrypted Macs';" | gauge count(c.computer_id) 1 10 20 30 40 50 60 70 80 90 100 200 300

The above search basically query’s the JSS for a specific smart group, in this case my Encrypted macs smart group, and then reports back the number of machines in that group, pretty simply right. But splunk offers very cool ways of displaying data, so what I did is appended a radial guage to the search so that it displays a graphical speedometer like reading. I have this search saved, and in splunk I have created a custom view attached to this search. Below is the xml for the custom view.

<view template="dashboard.html">
<module name="HiddenSavedSearch" autoRun="True" layoutPanel="panel_row1_col1">
    <param name="savedSearch">Encrypted Macs Progress</param>

    <module name="JobProgressIndicator">
      <module name="HiddenChartFormatter">
        <param name="chart">radialGauge</param>
          <param name="chartTitle">Encrypted Macs Progress (RealTime)</param>


          <module name="JobProgressIndicator"/>

            <module name="FlashChart">

          </module>
       </module>
   </module>
</module>

</view>

external image link

I also modified the setting on the saved search to update in splunk every minute, that way the data would always be up-to-date when viewed.

One difficulty I ran into was that the custom view that I created could only be viewed in splunk, which didn’t make sense because the whole point of creating this was for management to view data, not for them to log into another system that they were unfamiliar with, etc etc.

So I followed the below splunk article for Enabling insecure login so that I could have someone view the page without logging into splunk. http://docs.splunk.com/Documentation/Splunk/5.0.2/AdvancedDev/3rdParty

The only issue is that the password to the insecure login is passed in clear text, I’ve yet to investigate this but if anyone has a better solution please share.

Once I setup insecure logon, I basically grabbed the URL of the splunk graph which in my case was: http://myserver-01.mycompany.net:8000/account/insecurelogin?username=admin&password=admin&return_to=%2Fen-US%2Fapp%2FMySQL%2FEncrypted_Macs_Progress
And just attatched it to an iframe in sharepoint.

That’s it have fun.

[franton]

That's very helpful! We're starting to investigate Splunk. I'll pass your article onto the systems team member dealing with this.

[donmontalvo]

Splunk on JDS with a side of bacon...

http://docs.splunk.com/Documentation/Splunk/5.0.2/Installation/InstallonLinux

[Bukira]

I followed this and got as far as the xml bit, how did you create the xml without all the rest of the guff on the page? my xml file looks nothing like yours or the example on their site,

<?xml version='1.0' encoding='utf-8'?>

<dashboard> <label>is Online</label> <row> <chart> <searchName>Is Online</searchName> <title>Screens Online</title> <option name="charting.chart">radialGauge</option> <option name="charting.chart.rangeValues">[0,7]</option> <option name="charting.chart.style">shiny</option> <option name="charting.gaugeColors">[0x84e900]</option> </chart> </row>
</dashboard>

this is mine and looks nothing like yours

glad of any help

[solomonacquah]

Hi Bukira,

In order to create a custom dashboard without all the fluff you need to use advanced XML. see the splunk post below.
http://docs.splunk.com/Documentation/Splunk/5.0.2/AdvancedDev/AdvancedDashboard

I've modified your XML code below.

<view template="dashboard.html">
<module name="HiddenSavedSearch" autoRun="True" layoutPanel="panel_row1_col1">
    <param name="savedSearch">Is Online</param>

    <module name="JobProgressIndicator">
      <module name="HiddenChartFormatter">
        <param name="chart">radialGauge</param>
          <param name="chartTitle">Screens Online</param>
          <param name="charting.chart">radialGauge</param>
         <param name="charting.chart.style">shiny</param>
        <param name="charting.chart.rangeValues">[0,7]</param>
        <param name="charting.gaugeColors">[0x84e900]</param>

            <module name="JobProgressIndicator"/>

            <module name="FlashChart">

          </module>
       </module>
   </module>
</module>

</view>

[Bukira]

Thanks for that, awesome,

Thats connecting to my local sqlite database, however when i try to connect to my JSS i cant,

Im using my test server and the default account which i have granted access to my splunk server,

GRANT SELECT ON jamfsoftware.* TO username@serverip IDENTIFIED BY 'password';

i get this error everytime i try

Encountered the following error while trying to update: Splunkd daemon is not responding: ('The read operation timed out',)

ive rebooted splunk but no go

i can access fine with mysql workbench, couldn't find any answers on google either,

[solomonacquah]

@Bukira

Let me make sure I understand. Your mysql query against the JSS works fine, but when you perform your search in splunk via the mysqlconnector you get the error mentioned??

Have you checked the splunk dispatch folder it may be full?

Also everytime you perform a search in splunk it queues a search job you may have too many jobs queued up, you need to remove them.

[Bukira]

Hi,

No thats not right, i can access my JSS from the server using mysql workbench so i know that my access is correct, however when i try and create a New external database connection using the Splunk DB connect App in Splunk i get :

Encountered the following error while trying to update: Splunkd daemon is not responding: ('The read operation timed out',)

I have tried connecting to two different JSS servers and the same on both every time i try

I can access a local sqlite database no problem using the same Splunk DB Connect App

[chris_kemp]

The nitpicker in me wants to point out that Splunk is not completely free - you can process 500 MB of data per day, at which point it stops unless you buy a license. Something to keep in mind if you're pulling heavy reports. :~)

This is relevant to my interests, however - I might have to set up another Splunk server for Casper.

[Bukira]

Very true and was my thoughts exactly, i wanna see if i can use it first, plus i doubt i have much to process each day, theres no prices on it on their site, ive emailed for an education price but nothing yet

[chris_kemp]

We have a Splunk server set up here for some periodic log analysis, but we have to be selective about what we use it for. Still nice to have, though.

[jhbush]

Just FYI http://www.youtube.com/watch?v=0vdddr4AydU&feature=youtu.be
http://splunk-base.splunk.com/apps/50803/splunk-db-connect

[tomt]

Reviving this older post. Our IT Security group has asked me if there is a way to feed Casper logs into Splunk. Just wondering if anyone has found an easy way to do this in the year since this was originally posted?

Thanks,
Tom

[TJ0214]

Has anyone done this recently? I am getting a Unknown error while validating database connection. This would be in the Splunk DB Connector. If anyone has some expertise in this would love to talk to them.

Thanks

[TJ0214]

So I have found out the Java Bridge is not running. Tried to update JRE and no luck. Anyone have Java Bridge errors? How did you get by them

[karthikeyan_mac]

Hi,

We have installed Splunk DB Connect V2 to connect our MySQL DB to query the tables to provide the reports using the Splunk reports. I am able to query the MySQL DB and can see the query output in Search but not able to create Pivot based on the Search Output.

| dbxquery query ="SELECT DISTINCT * From computers_denormalized" connection="my connection"

Does anyone using Splunk DB Connect V2 and creating reports?

Thanks & Regards,
Karthikeyan M