Solved

Link SQL table to Webpage

Posted on 2016-11-11
9
34 Views
Last Modified: 2016-11-14
So i have a script that writes to an SQL express database table mostly for auditing and alerting purposes, I did it like this because it was too large as an CSV for excel, But I'd still like to view and manipulate the data.  Is there a way to do this with HTML/IIS.  It can either be its own page or a embedded table thats searchable.  I have never messed with this type of scenario, but I think it would be helpful to learn.  Any help would be wonderful
0
Comment
Question by:leadtheway
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Please show us the script that writes to the SQL express database and some representative test data, thanks.
0
 
LVL 2

Expert Comment

by:Mike in IT
Comment Utility
There are a lot of different ways that you can accomplish this. Most of them require a lot of programming in the back end, but will make it look nice on the front end. Since you're using PHP it wouldn't be that difficult to create a table in a webpage that will show you everything in your DB table, and even to setup pagination so that it's not just one long list on your webpage. You could use something like DataTables to make it easily searchable, though their Edit plugin is a paid service, you could just create your own.

Though like Ray says we need more information to be able to actually help you with something that will work for you.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
> Is there a way to do this with HTML/IIS.

Html of course can't output data, only display. You will need to rely on sql server or server side code for that. But once you do have data, you can use html and javascript to create charts and spreadsheets either by hand or preferably with a library. Some examples are https://handsontable.com/ or http://www.chartjs.org/. There are a lot of other options here.   Using these types of tools does take some expertise in either using php (or .NET) or sql server to get the data to an html page.

Take a look at some reporting tools that can connect to your database and allow you to easily generate reports.  These may be much easier to use if you are not a developer.

https://www.domo.com/
https://www.qlik.com/ and see the free version https://www.qlik.com/us/products/qlikview/personal-edition.
http://www.tableau.com/  I have tested this out. I like the mapping features if your data is spatial.
https://doc.periscopedata.com/doc/
1
 
LVL 1

Author Comment

by:leadtheway
Comment Utility
yes, i only need to display, SQL is the backend  this is the complete script including connection info

$ConnectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"

function WriteSQL ($query)
{
    if ($debug -eq $true) {Write-Host $query}
    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $Connection.ConnectionString = $connectionString
    $Connection.Open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $command.Connection = $Connection
    $command.CommandText = $query
    $command.ExecuteNonQuery()
    $connection.Close()
}

$ErrorActionPreference = "Continue" 
$strComputer = $env:ComputerName 
$colDrives = Get-PSDrive -PSProvider Filesystem 
ForEach ($DriveLetter in $colDrives) { 
    $StartPath = "E:\Share\" 
    Get-ChildItem -LiteralPath $StartPath -Recurse -Directory | 
    ForEach { 
      $FullPath = Get-Item -LiteralPath (Get-Item -LiteralPath $_.PSPath) 
      (Get-Item -LiteralPath $FullPath).GetAccessControl() | 
      Select * -Expand Access | where {$_.IsInherited -notlike "true" } |
      Select @{N='ServerName';E={$strComputer}}, 
             @{N='FullPath';E={$FullPath}}, 
             @{N='Type';E={If($FullPath.PSIsContainer -eq $True) {'D'} Else {'F'}}}, 
             @{N='Owner';E={$_.Owner}}, 
             @{N='Trustee';E={$_.IdentityReference}}, 
             @{N='Inherited';E={$_.IsInherited}}, 
             @{N='InheritanceFlags';E={$_.InheritanceFlags}}, 
             @{N='AceFlag';E={$_.PropagationFlags}}, 
             @{N='AceType';E={$_.AccessControlType}}, 
             @{N='AccessMasks';E={$_.FileSystemRights}} } |

             %{
                $query = "INSERT INTO $tableName (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks) VALUES ('$($_.servername)','$($_.fullpath)','$($_.type)','$($_.owner)','$($_.trustee)','$($_.inherited)','$($_.inheritanceflags)','$($_.aceflag)','$($_.acetype)','$($_.accessmasks)')"
                WriteSQL $query
             }

    }
             

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:leadtheway
Comment Utility
and i haven't set on a front end, I'd like something that can be manipulated through web browser, Whichever is easier and repeatable
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
What programming language are you using in this code snippet?  You can ask a moderator to put your question into that Zone, and you will get more eyes on the question.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
Ray, that looks like Windows Powershell. For what it is worth, you can hit php pages via Powershell like below. I have some scheduled tasks I run this way.
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -c "(new-object system.net.webclient).downloadstring('https://domain.com/path_to/page.php')"

Open in new window


@leadtheway, I think Ray was asking about any PHP code that you may have been using.  

It looks like there are 3 parts here.  1) Sending data to sql server (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks)  2) Accessing the data from sql server and 3) Viewing the data from a web browser.

Can you help us understand about your use better?  About how many rows of data get sent to sql server? Is that a static  number or will it increase by x every month?  How do you need to manipulate the data, view, sort, filter?  If we assist you with a php option for accessing the database are you able to follow or do you need assistance with writing something from scratch?  (if so, please see https://www.experts-exchange.com/gigs/ or https://www.experts-exchange.com/live/ as we typically will help with code here and not always write a complete solution from scratch).  I think once we get past step 2, you can choose the front end..

Let's try and make this thread focus on just one item. If you need help accessing data, we can assist with that, then ask a new question for the 3rd part to keep things succinct.
0
 
LVL 1

Author Comment

by:leadtheway
Comment Utility
the number of lines will vary depending on what i run the script against, for this current table its about 4k table rows.  I was hoping there was something simple i could drop in an HTML page that would pull this data and be able to view and sort it.  I might have to try visual studio, i already have the database connected to it, just getting into a usable format
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
Comment Utility
With 4,000 rows of data, use sql manager studio to export to excel.  That would be the easiest.
1

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now