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!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...