Welcome, Guest. Please login or register.

Author Topic: Report as SQL statement  (Read 1673 times)

kai

  • Newbie
  • *
  • Posts: 32
    • View Profile
Report as SQL statement
« on: October 10, 2014, 07:15:23 AM »
Hello,

I must create a report for an external Tool.
For this I need the Report Interfaces "IF Active Now" as SQL query with a time range.

Thanks
Kai

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2526
    • View Profile
    • NeDi
Re: Report as SQL statement
« Reply #1 on: October 10, 2014, 11:39:49 AM »
As admin click on the ladybug icon in the top right. The debug output shows SQL queries, which can easily be edited in System-Export...
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

kai

  • Newbie
  • *
  • Posts: 32
    • View Profile
Re: Report as SQL statement
« Reply #2 on: October 13, 2014, 08:03:02 AM »
Thanks,

I found this, now I need a relation to timerange.
For example, I need all active ports for last mont per device.

Thanks
Kai

kai

  • Newbie
  • *
  • Posts: 32
    • View Profile
Re: Report as SQL statement
« Reply #3 on: October 14, 2014, 07:51:45 AM »
Hello,

I think this is a working SQL statement, when I start thi on every first day of month:

SELECT device,count(*) as cnt, (sum((lastchg = 0 and ifstat = 3) or (lastchg > 1412114400 and ifstat <> 3) or (ifstat = 3))) AS actif FROM interfaces LEFT JOIN devices USING (device) where iftype regexp '^(6|7|117)$' AND services &2= 2  GROUP BY device ORDER BY actif desc;

Regards
Kai

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2526
    • View Profile
    • NeDi
Re: Report as SQL statement
« Reply #4 on: October 16, 2014, 10:36:23 AM »
Cool, this can also be useful for a LAAS (LAN as a service) solution! Tx, for sharing...
« Last Edit: October 16, 2014, 10:42:06 AM by rickli »
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo