I know it sounds grammatically incorrect … it should say WHAT IS SSRS REPORT ? But this title is very intentional. It just says that WHO IS SSRS REPORT, means “Who Is” report in SSRS. “Who IS” is a query that is used to find details of registration information of any given public IP address.
Recently I helped our network guy in creating a security report. We were using user access log stored in database to generate some sort of simple report to display what / who / how connected to our system using VPN. This report also included an external IP of users who connected using VPN. Today I was just thinking about creating something cool in SSRS, and it came to my mind …. how cool that will be if you can just click on IP of user and get more details of it … like location ?? Or name of City/State ?? I know it is not so easy to get accurate information about users IP because most of the time ISPs have some remote location which is being used as gateway (bare with me, I am not an expert of this topic) and due to that user IP are usually not so accurate (or TOR lovers like me).
Ok, so about report. Actually it is pretty simple report with just an extra kick. For this demo, I created a table which was using following query,
CREATE TABLE [dbo].[loginLog](
[userId] [varchar](100) NULL,
[userIP] [varchar](100) NULL
) ON [PRIMARY]
-- Some dummy data
-- These IPs are of Google’s
Insert into loginLog values('User1' ,'66.102.0.0')
Insert into loginLog values('User6','66.102.0.0')
Insert into loginLog values('User2','66.102.15.255')
Insert into loginLog values('User4','74.125.0.11')
Insert into loginLog values('User','209.85.128.90')
This table contains only two columns. One is login name and another is IP of user. Then create a simple report which shows two fields.In a designer view my report looked something like below, If you don’t know how to create a report using BIDS (Business Intelligence Development Studio) or SSDT (SQL Server Data Tools) then read my previous post about it.
Now there are few free services available on internet which provides Who IS API. These services can be used to query “Who IS” for any given IP. For this report, I used similar service from IP INFO DB. You will need to register in order to get API key which have to be used in order to query results. Basic syntax for this query is something like this,
http://api.ipinfodb.com/v3/ip-city/?key=<your_api_key>&ip=209.85.128.90
It excepts some additional parameters too. The query I used finally was looking something like this,to generate output as XML.
Next, is to create a simple parameter and store whole URL except IP part which we will feed from report. Set this report as “Internal” variable and have it’s default value as
http://api.ipinfodb.com/v3/ip-city/?key=<my API KEY>&format=XML&ip=
 |
 |
Next step is to make “User IP” column point to this url with IP. To do that right click on text box of “User IP” data field (not on header) and select “textbox properties” and select “action”. Then select “go to URL” as action. And click on function button to open up expression window. Here you will need to create an expression which is pretty straight forward,
=Parameters!WhoISURL.Value + Fields!userIP.Value
Expression is just adding IP in end of URL which we are using to run “who is” query.
 |
 |
And that’s it !! Now hit preview button and click on any IP. It will open up new window and run query for IP which we just clicked.
That’s it for this post. Now we can use this XML as datasource to create another report which will display data in form other than XML. But I don’t think it can be done using just simple SSRS, you will need to use some kind of programming technique to achieve that result. For me, it served my purpose so I am just going to leave it here.
If you want you can download project from here. (you will need to add your API key in parameter “WhoIsURL”).
It’s Just A Thought … 
