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

[willisiw]

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!!!

[nbourdoux]

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

[akocak]

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.

[inventsekar]

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.