All Apps and Add-ons

Can you help me query an SQL database for AirWatch with Splunk DB Connect?

willisiw
Explorer

I am using DBConnect to query a SQL database for AirWatch. The IP address and MAC address fields are in binary. I have converted to Decimal using the query and the data is in Splunk, but I am stuck there.

I need a way of getting the MAC and IP in proper dotted notations.

Query:

> SELECT 
>         CONVERT(int, iface.[MAC], 0) AS dest_mac_decimal ,
>          iface.[MAC] AS dest_mac_binary ,
>          CONVERT(int, iface.[IPAddress], 0) AS
> dest_ip_decimal ,
>          iface.[IPAddress] AS dest_ip_binary ,
>         iface.[Name] AS dest_int_name ,
>         iface.[Description] AS dest_int_desc ,
>         iface.[IsWLAN] AS dest_int_wlan ,
>          dev.* 
>       FROM "AirWatch"."dbo"."Device" AS dev 
>       LEFT OUTER JOIN [AirWatch].[dbo].[DeviceInterface] AS
> iface
>     ON dev.DeviceID = iface.DeviceID

The data is searchable and here is my search:

>sourcetype=airwatch:inventory dest_ip_decimal=* dest_mac_decimal=*

Result:

> 2018-11-19
> 12:00:00.076  dest_mac_decimal="931904946"    dest_ip_decimal="168559919"
> 2018-11-19
> 12:00:00.076  dest_mac_decimal="884467629"    dest_ip_decimal="168559915"
> 2018-11-19
> 12:00:00.074  dest_mac_decimal="1214041724"   dest_ip_decimal="168559694"
> 2018-11-19
> 12:00:00.070  dest_mac_decimal="-1477460740"  dest_ip_decimal="168559657"
> 2018-11-19
> 12:00:00.070  dest_mac_decimal="-396175749"   dest_ip_decimal="168559881"

Notice some mac are somehow converted to a negative number.

THANKS!!!

0 Karma

nbourdoux
New Member

Here is your answer (MacAddress is in another table, IPaddress just need to be converted from HexaDecimal):

SELECT 
          attrib.macaddress AS macAddress ,
          CONCAT( CONVERT(int, SUBSTRING(iface.[IPAddress], 1,1)), '.', CONVERT(int, SUBSTRING(iface.[IPAddress], 2,1)), '.', CONVERT(int, SUBSTRING(iface.[IPAddress], 3,1)), '.', CONVERT(int, SUBSTRING(iface.[IPAddress], 4,1))) as IP,
          iface.[Name] AS dest_int_name ,
          iface.[Description] AS dest_int_desc ,
          iface.[IsWLAN] AS dest_int_wlan ,
           dev.* 
        FROM "AirWatch"."dbo"."Device" AS dev 
        LEFT OUTER JOIN [AirWatch].[dbo].[DeviceInterface] AS iface   ON dev.DeviceID = iface.DeviceID
        inner join [AirWatch].[dbo].deviceattributes as Attrib on dev.DeviceID = Attrib.DeviceID
0 Karma

akocak
Contributor

If I were you, I would convert variables to String instead of Integer. In most of the cases on Splunk side, data type is not that questioned until you get to calculation or string manipulation.

Only thing I would send from DB to Splunk in decimal would be Epoch time that I can use it to track last row that I had input.

0 Karma

inventsekar
SplunkTrust
SplunkTrust

Hi, the DBConnect App will convert the SQL query to Splunk Query automatically.
Please share with us the Splunk query for your current situation, so that the eval command can be implemented inside that query.

thanks and best regards,
Sekar

PS - If this or any post helped you in any way, pls consider upvoting, thanks for reading !
0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...