Jump to content
marc.spears

Asset Information to Extra Data Field

Recommended Posts

Every month we are pulling together information and making reports for management that detail a load of Asset Information like:

-How many servers does each client have

-How many workstations does each client have

-How many are physical/virtual

-How many are laptops/desktops

 

I would love to have this information put into EDF tab on the Client Level. That way we could just open up the client and take a screenshot of the figures, or even have it email a report of the data every month.

 

I've got the EDF's laid out like I want but I'm having trouble with the SQL.

 

For starts, I'd like to know how many workstations client x has, the caveat is they should not be a member of our 'no contract' or 'monitoring only' locations.

 

For that, I can pull the location id of the above but I can't figure out how to compare that against the list of servers/workstations for client xyz.

 

Something like SELECT COUNT(*) FROM computers WHERE clientid= '%clientid%' will give me a count that I can input into the EDF. My question is can someone help me figure out how to compare that data against the location id's I'd like to exclude?

 

Thanks in advance!

 

Marc

Share this post


Link to post
Share on other sites

Searches came in handy, big time. I created Searches to find all the info I needed and then copied their SQL (editing it to give a count(*) instead of a list).

 

I can share all the queries on here if anyone is interested.

 

Here is one to find all Virtual Servers for a Client that are not members of specific Locations:

 

SELECT DISTINCT COUNT(*)
FROM Computers, Clients, Locations 
WHERE Computers.ClientID = Clients.ClientID 
AND Locations.LocationID = Computers.LocationID 
AND ((Clients.Name = '%clientname%') 
AND (Computers.OS LIKE '%server%') 
AND (Locations.`Name` NOT LIKE '%BDR%') 
AND (Locations.`Name` NOT LIKE '%Monitoring Only%') 
AND (Locations.`Name` NOT LIKE '%No Contract%')
AND (Locations.`Name` NOT LIKE '%New Computers%') 
AND ((Computers.Flags & 2048) = 2048))

Once I get the scripts in place to automate this, I'll post those too.

Share this post


Link to post
Share on other sites

Script "Gather Asset Information" is the one I schedule, it will call the secondary script "Gather Asset information - Update EDF's"

 

 

 

 

This is done so that the values can be set from a FOR EACH SQL command which passes the secondary script to each result.

 

Script "Gather Asset Information - Create Ticket" is the one I schedule, it calls secondary script "Gather Asset Information - Update Ticket"

 

Again, this is done so that the secondary script can be run against the results of the first's SQL query. It appends it's findings into one ticket.

 

The end result is I have EDF's that have up-to-date Asset Information as well as monthly tickets which report particular clients Asset Information. For privacy reasons, I have removed client names from portions of the scripts.

 

If you have any questions about how to get this going, or recommendations, post them here. I'd be glad to hear from you.

Gather Asset Information.zip

Copy of Gather Asset Information - Update EDFs.zip

Edited by Guest

Share this post


Link to post
Share on other sites

Marc,

 

I was able to import scripts successfully and have the EDF's, no problem. How are you going about scheduling/running the Gather Asset Information - Create Ticket script? Are you running against a specific Group or computer(s)? I'm trying to follow the logic here.

 

:!: I'm also seeing opportunity to expand on this idea?

 

FYI - you might want to make copies of your scripts and cleanup the client names before exporting and posting :| .

 

Thanks.

 

-jeff

Share this post


Link to post
Share on other sites

Jeff,

 

Thanks for checking these out! I have a group set aside for my LT server and schedule several scripts against it to automate and monitor a few things (LT database size, broken internal monitors, unsynced tickets, etc). I just added these to run against that group so they are running against the LT server directly.

 

Expanding the idea? I'm intrigued! What do you have in mind?

 

Marc

Share this post


Link to post
Share on other sites

Marc,

 

Thanks for the reply. I ran this against the LT Server and am not getting any results. Any other configurations required other than creating the EDF's, importing scripts, and running the Gather Asset Information - Create Ticket against the LT Server?

 

If I can get this working, I'm thinking about expanding to include total # number of agents deployed with LT integrated solutions such as HMP, TVR, SP Server, SP Desktop, etc., since LT does not have a way of doing this currently.

 

-jeff

Share this post


Link to post
Share on other sites

Jeff,

 

Now that's a really cool direction to take it! Have you run the "Gather Asset Information" script?

 

It executes the FOR EACH SQL query that runs the "Gather Asset Information - Update EDF's" script against the results. This is is the one that actually does most of the work and puts Asset Info into the EDF's.

 

The "Gather Asset Information - Create Ticket" script just grabs that info out of the EDF's, formats them, then puts them into a ticket.

 

Here's a screenshot of how I have things scheduled out on my system:

 

59ec9432dd91d_ScriptsSchedule.PNG.b5242b46ff255b3aaf98b2a4a8ff4d9e.PNG

Share this post


Link to post
Share on other sites

Thanks Marc. I got this working. I inadvertently failed to import 1 script. Now that I have all the scripts, it works great. Thanks for this. I'll spend more time testing and reviewing and see what I can do to expand on this. I'll post any changes as I come up with it. Happy weekend :)

 

-jeff

Share this post


Link to post
Share on other sites

Marc,

 

Can you elebaorate on what your defined EDF for Server Firewalls is looking for? Thanks.

 

FYI - starting on the development of the CCleaner, ESET, TVR, HMP, SP Counts now :)

 

I'll post results if I have any success...LOL.

 

Thanks.

 

-jeff

Share this post


Link to post
Share on other sites

Hey Jeff,

 

The reasoning behind the "Server Firewalls" EDF was that one of our clients has a server that they are using purely as a software firewall between two LAN's. Since it's only purpose is to act as a firewall, we bill them less for it than we would a managed server so I needed a way to notate that in the Asset Info.

 

I explicitly set the value, only for that client, in the gather update scripts. (it's looking for that "server firewall" by name).

 

It's probably best to ignore that EDF and its corresponding steps in the scripts. I left it in there thinking that there might be a need for someone else to explicitly define a value for one of their clients.

 

I'm excited to hear how your development's coming along!

 

Marc

Share this post


Link to post
Share on other sites

Thanks for that feedback Marc. I sorta of expected it was for something of the sorts based on the review of scripts. I have pulled it out for now, but will likely repopulate with the other items I hope to add. I am working on trying to add HMP, TVR, MDM, CCleaner, SP Server, SP Desktop, ESET FS, and ESET EP. The hope will be to pull the count based on what is installed and licensed per client. If I have any success on the SQL Queries (not my expertise), I will let you know. Once I get these ironed out, I will be creating a Cyrstal Report on this so I can schedule a monthly report in a nice presentation format.

 

-jeff

Share this post


Link to post
Share on other sites

Great script!

 

Have a question for you regarding the create ticket script. There is a line "SELECT NAME,clientid FROM clients WHERE name IN ('@ManagedClients@')"

I am not getting an email or a ticket generated. Is this because of the @ManagedClients@ ?? Where exactly is it pulling that from?

Thanks!

Share this post


Link to post
Share on other sites

I have updated the "Gather Asset Information - Update EDFs" script to collect SNMP printer and switch counts

 

Check lines 47 to 52 in the script below.

You can easily change it to handle ESET counts or VMhost counts which I will do once I get all my VMHosts added to LabTech.

 

https://www.cubbyusercontent.com/pli/NST+-+Gather+Asset+Information+-+Update+EDFs.xml/_ee423a7459b3482b94726dbb1ee04b9b

59ec9434a6817_assetpage.JPG.7ceab49d24f8999742f65223f9deeef8.JPG

Share this post


Link to post
Share on other sites

Ben,

 

I have 2 queries I made for AV counts. I specifically wanted a count for ESET only for workstations and servers. Hope the below helps

 

Server:

SELECT COUNT(computerid) FROM computers WHERE virusscanner IN (SELECT virusscanners.vscanid FROM virusscanners WHERE virusscanners.name LIKE '%eset%') AND os NOT LIKE '%server%' AND clientid IN (SELECT clientid FROM clients WHERE NAME = '%sqlname%')

 

Workstations:

SELECT COUNT(computerid) FROM computers WHERE virusscanner IN (SELECT virusscanners.vscanid FROM virusscanners WHERE virusscanners.name LIKE '%eset%') AND os LIKE '%server%' AND clientid IN (SELECT clientid FROM clients WHERE NAME = '%sqlname%')

Share this post


Link to post
Share on other sites

I added this andd ti works great. looking to add field d for hitmanpro, anyone have any ideas on what that query would look like?

Share this post


Link to post
Share on other sites
Jeff,

 

Is there any development on furthering the script to include ESET Counts?

 

 

Ben

 

 

Ben,

 

Sorry I missed this and I know it's old thread, but no, I never completed ESET since we switched to Webroot. I did however, add in Webroot to the inventory.

 

jeff

Share this post


Link to post
Share on other sites

i updated to include ESET workstations, ESET Servers, and hitmanpro workstations

 

ESET workstations

SELECT COUNT(computerid) FROM computers WHERE virusscanner IN (SELECT virusscanners.vscanid FROM virusscanners WHERE virusscanners.name LIKE '%eset%') AND os NOT LIKE '%server%' AND clientid IN (SELECT clientid FROM clients WHERE NAME = '%sqlname%')

ESET servers

SELECT COUNT(computerid) FROM computers WHERE virusscanner IN (SELECT virusscanners.vscanid FROM virusscanners WHERE virusscanners.name LIKE '%eset%') AND os LIKE '%server%' AND clientid IN (SELECT clientid FROM clients WHERE NAME = '%sqlname%')

hotmanpro

SELECT DISTINCT COUNT(*)

FROM Computers, Clients, Locations, v_extradatacomputers

WHERE Computers.ClientID = Clients.ClientID

AND Locations.LocationID = Computers.LocationID

AND ((Clients.Name = '%sqlname%')

AND (Computers.OS NOT LIKE '%server%')

AND (Locations.`Name` NOT LIKE '%No Contract%')

AND (Locations.`Name` NOT LIKE '%Monitoring Only%')

AND (Locations.`Name` NOT LIKE '%BDR%')

AND (Locations.`Name` NOT LIKE '%New Computers%')

AND (v_extradatacomputers.ComputerID = Computers.ComputerID)

AND (v_extradatacomputers.`Licensed` = 1))

Share this post


Link to post
Share on other sites

i am trying to tally Apple machines but it is not saving results

 

SELECT DISTINCT COUNT(*)

FROM Computers, Clients, Locations

WHERE Computers.ClientID = Clients.ClientID

AND Locations.LocationID = Computers.LocationID

AND ((Clients.Name = '%sqlname%')

AND (Computers.OS like '%Darwin%'))

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...