Active_prgs/Redgrave/EndoIGServerAnalyzer.py

"""

EndoIGServerAnalyzer

Created by:
Emanuel Borges
07.28.2022

This program will take two CSV files and do a row to row compare.  For this to work both CSVs need to have the same colums.
Set colOmitList to FALSE if you dont want it to parse at all and remove any columns.  for that to work though you have to normalize the date
and number fields.

NOTE: the application List file HAS pipes in it, so carefully select which columns you use.

"""

import os


if __name__ == '__main__':

    outputFileName = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\20220804-WindowsServer8_Matrix_Report.csv"

    ## This should all be csvs with pre-selected fields that are verified didnt already contain pipes. Also a headder row.
    #win2kServersFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\Win8Servers_filtered.csv"
    win2kServersFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\Win8Servers_NOFilter.csv"

    appListServersFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\AppInventory_extraFields.csv"
    sqlServersListFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\Endo SQL Servers List__As of July 2022.csv"
    prevSqlServersListFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\SQL Server Report 07212021.csv"
    aspectDatabasesToServersFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\All_endo_SQL_Server_databases.csv"

    win2kServersFileMatrix = {}
    appListServersFileMatrix = {}
    sqlServersListFileMatrix = {}
    prevSqlServersListFileMatrix = {}
    aspectDatabasesToServersMatrix = {}

    ## Start with ingesting the win2kservers file but be sure to save the headders on this one.
    contents = open(win2kServersFile).readlines()
    win2kServersFileHeadders = contents[0]
    win2kServersFileHeadders = win2kServersFileHeadders.replace("\n","")
    contents = contents[1:]
    for line in contents:
        line = line.replace("\n","")
        parsedLine = line.split("|")
        if parsedLine[0].upper() in list(win2kServersFileMatrix.keys()):
            win2kServersFileMatrix[parsedLine[0].upper()].append(line)
        else:
            win2kServersFileMatrix[parsedLine[0].upper()] = [line,]
    print("There are %s servers to start with."%len(list(win2kServersFileMatrix.keys())))


    ## Next ingest the SQL servers list
    contents = open(sqlServersListFile).readlines()
    contents = contents[1:]
    for line in contents:
        line = line.replace("\n","")
        parsedLine = line.split("|")
        if parsedLine[0].upper() in list(sqlServersListFileMatrix.keys()):
            sqlServersListFileMatrix[parsedLine[0].upper()].append(line)
        else:
            sqlServersListFileMatrix[parsedLine[0].upper()] = [line,]
    print("There are %s servers on the SQL Servers file."%len(list(sqlServersListFileMatrix.keys())))


    ## Next ingest the older SQL servers list
    contents = open(prevSqlServersListFile).readlines()
    contents = contents[1:]
    for line in contents:
        line = line.replace("\n","")
        parsedLine = line.split("|")
        if parsedLine[0].upper() in list(prevSqlServersListFileMatrix.keys()):
            prevSqlServersListFileMatrix[parsedLine[0].upper()].append(line)
        else:
            prevSqlServersListFileMatrix[parsedLine[0].upper()] = [line,]
    print("There are %s servers on the older SQL servers file."%len(list(prevSqlServersListFileMatrix.keys())))


    ## Next ingest the databasename to server list from aspect
    contents = open(aspectDatabasesToServersFile).readlines()
    contents = contents[1:]
    for line in contents:
        line = line.replace("\n","")
        parsedLine = line.split("|")
        if parsedLine[0].upper() in list(aspectDatabasesToServersMatrix.keys()):
            aspectDatabasesToServersMatrix[parsedLine[0].upper()].append(line)
        else:
            aspectDatabasesToServersMatrix[parsedLine[0].upper()] = [line,]
    print("There are %s servers on the aspect databases to servers file."%len(list(aspectDatabasesToServersMatrix.keys())))

    ## Next is the app inventory which must first be parsed.
    contents = open(appListServersFile).readlines()
    contents = contents[1:]
    for line in contents:
        line = line.replace("\n","")
        parsedLine = line.split("|")
        parsedServerList = parsedLine[4].replace("#","")
        parsedServerList = parsedServerList.split(";")
        for s in parsedServerList:
            if s.isnumeric():
                pass
            else:
                if s.upper() in list(appListServersFileMatrix.keys()):
                    appListServersFileMatrix[s.upper()].append(line)
                else:
                    appListServersFileMatrix[s.upper()] = [line,]
    print("There are %s servers on the application list."%len(list(appListServersFileMatrix.keys())))


    outputFile = open(outputFileName,'w')
    outputFile.write(win2kServersFileHeadders + "|Found on 2022 SQL Server Report?|Found on 2021 SQL Server Report?|Found DatabaseNames Server Report?|DB Names Found on AspectList|Found on Application Inventory?|Applications\n")
    for i in list(win2kServersFileMatrix.keys()):
        nl=outputFile.write(win2kServersFileMatrix[i][0])
        
        if i in list(sqlServersListFileMatrix.keys()):
            nl=outputFile.write("|YES")
        else:
            nl=outputFile.write("|NO")
        if i in list(prevSqlServersListFileMatrix.keys()):
            nl=outputFile.write("|YES")
        else:
            nl=outputFile.write("|NO")
        dbNamesList = ""
        if i in list(aspectDatabasesToServersMatrix.keys()):
            nl=outputFile.write("|YES")
            for dbNameLine in aspectDatabasesToServersMatrix[i]:
                dbNameLine = dbNameLine.replace("\n","")
                dbNameLine = dbNameLine.split("|")
                if dbNamesList:
                    dbNamesList = dbNamesList + "; " +dbNameLine[1]
                else:
                    dbNamesList = dbNameLine[1]
        else:
            nl=outputFile.write("|NO")
        if dbNamesList:
            nl=outputFile.write("|" + dbNamesList)
        else:
            nl=outputFile.write("|")
        appList = ""
        if i in list(appListServersFileMatrix.keys()):
            nl=outputFile.write("|YES")
            for appLine in appListServersFileMatrix[i]:
                appLine = appLine.replace("\n","")
                appLine = appLine.split("|")
                if appList:
                    appList = appList + '; ' + appLine[1]+' [AppID=%s]'%appLine[0]
                else:
                    appList = appLine[1]+' [AppID=%s]'%appLine[0]
        else:
            nl=outputFile.write("|NO")
        if appList:
            nl=outputFile.write("|" + appList)
        else:
            nl=outputFile.write("|")
        nl = outputFile.write("\n")
    outputFile.close()
Revision:	771
Committed:	Thu Aug 4 18:31:02 2022 UTC (3 years, 7 months ago) by nino.borges
Content type:	text/x-python
File size:	7697 byte(s)
Log Message:	First working version of this program to extract data from various reports for windows 2008 server analysis.
#	User	Rev	Content
1	nino.borges	771	"""
2
3			EndoIGServerAnalyzer
4
5			Created by:
6			Emanuel Borges
7			07.28.2022
8
9			This program will take two CSV files and do a row to row compare. For this to work both CSVs need to have the same colums.
10			Set colOmitList to FALSE if you dont want it to parse at all and remove any columns. for that to work though you have to normalize the date
11			and number fields.
12
13			NOTE: the application List file HAS pipes in it, so carefully select which columns you use.
14
15			"""
16
17			import os
18
19
20			if __name__ == '__main__':
21
22			outputFileName = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\20220804-WindowsServer8_Matrix_Report.csv"
23
24			## This should all be csvs with pre-selected fields that are verified didnt already contain pipes. Also a headder row.
25			#win2kServersFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\Win8Servers_filtered.csv"
26			win2kServersFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\Win8Servers_NOFilter.csv"
27
28			appListServersFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\AppInventory_extraFields.csv"
29			sqlServersListFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\Endo SQL Servers List__As of July 2022.csv"
30			prevSqlServersListFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\SQL Server Report 07212021.csv"
31			aspectDatabasesToServersFile = r"C:\Users\eborges\Documents\Cases\Endo\20220715 - IG Projects\4 - Develop Identification Process to Inventory NCDS stored in Endo IT\_fromEndo\_csv\All_endo_SQL_Server_databases.csv"
32
33			win2kServersFileMatrix = {}
34			appListServersFileMatrix = {}
35			sqlServersListFileMatrix = {}
36			prevSqlServersListFileMatrix = {}
37			aspectDatabasesToServersMatrix = {}
38
39			## Start with ingesting the win2kservers file but be sure to save the headders on this one.
40			contents = open(win2kServersFile).readlines()
41			win2kServersFileHeadders = contents[0]
42			win2kServersFileHeadders = win2kServersFileHeadders.replace("\n","")
43			contents = contents[1:]
44			for line in contents:
45			line = line.replace("\n","")
46			parsedLine = line.split("\|")
47			if parsedLine[0].upper() in list(win2kServersFileMatrix.keys()):
48			win2kServersFileMatrix[parsedLine[0].upper()].append(line)
49			else:
50			win2kServersFileMatrix[parsedLine[0].upper()] = [line,]
51			print("There are %s servers to start with."%len(list(win2kServersFileMatrix.keys())))
52
53
54			## Next ingest the SQL servers list
55			contents = open(sqlServersListFile).readlines()
56			contents = contents[1:]
57			for line in contents:
58			line = line.replace("\n","")
59			parsedLine = line.split("\|")
60			if parsedLine[0].upper() in list(sqlServersListFileMatrix.keys()):
61			sqlServersListFileMatrix[parsedLine[0].upper()].append(line)
62			else:
63			sqlServersListFileMatrix[parsedLine[0].upper()] = [line,]
64			print("There are %s servers on the SQL Servers file."%len(list(sqlServersListFileMatrix.keys())))
65
66
67			## Next ingest the older SQL servers list
68			contents = open(prevSqlServersListFile).readlines()
69			contents = contents[1:]
70			for line in contents:
71			line = line.replace("\n","")
72			parsedLine = line.split("\|")
73			if parsedLine[0].upper() in list(prevSqlServersListFileMatrix.keys()):
74			prevSqlServersListFileMatrix[parsedLine[0].upper()].append(line)
75			else:
76			prevSqlServersListFileMatrix[parsedLine[0].upper()] = [line,]
77			print("There are %s servers on the older SQL servers file."%len(list(prevSqlServersListFileMatrix.keys())))
78
79
80			## Next ingest the databasename to server list from aspect
81			contents = open(aspectDatabasesToServersFile).readlines()
82			contents = contents[1:]
83			for line in contents:
84			line = line.replace("\n","")
85			parsedLine = line.split("\|")
86			if parsedLine[0].upper() in list(aspectDatabasesToServersMatrix.keys()):
87			aspectDatabasesToServersMatrix[parsedLine[0].upper()].append(line)
88			else:
89			aspectDatabasesToServersMatrix[parsedLine[0].upper()] = [line,]
90			print("There are %s servers on the aspect databases to servers file."%len(list(aspectDatabasesToServersMatrix.keys())))
91
92			## Next is the app inventory which must first be parsed.
93			contents = open(appListServersFile).readlines()
94			contents = contents[1:]
95			for line in contents:
96			line = line.replace("\n","")
97			parsedLine = line.split("\|")
98			parsedServerList = parsedLine[4].replace("#","")
99			parsedServerList = parsedServerList.split(";")
100			for s in parsedServerList:
101			if s.isnumeric():
102			pass
103			else:
104			if s.upper() in list(appListServersFileMatrix.keys()):
105			appListServersFileMatrix[s.upper()].append(line)
106			else:
107			appListServersFileMatrix[s.upper()] = [line,]
108			print("There are %s servers on the application list."%len(list(appListServersFileMatrix.keys())))
109
110
111			outputFile = open(outputFileName,'w')
112			outputFile.write(win2kServersFileHeadders + "\|Found on 2022 SQL Server Report?\|Found on 2021 SQL Server Report?\|Found DatabaseNames Server Report?\|DB Names Found on AspectList\|Found on Application Inventory?\|Applications\n")
113			for i in list(win2kServersFileMatrix.keys()):
114			nl=outputFile.write(win2kServersFileMatrix[i][0])
115
116			if i in list(sqlServersListFileMatrix.keys()):
117			nl=outputFile.write("\|YES")
118			else:
119			nl=outputFile.write("\|NO")
120			if i in list(prevSqlServersListFileMatrix.keys()):
121			nl=outputFile.write("\|YES")
122			else:
123			nl=outputFile.write("\|NO")
124			dbNamesList = ""
125			if i in list(aspectDatabasesToServersMatrix.keys()):
126			nl=outputFile.write("\|YES")
127			for dbNameLine in aspectDatabasesToServersMatrix[i]:
128			dbNameLine = dbNameLine.replace("\n","")
129			dbNameLine = dbNameLine.split("\|")
130			if dbNamesList:
131			dbNamesList = dbNamesList + "; " +dbNameLine[1]
132			else:
133			dbNamesList = dbNameLine[1]
134			else:
135			nl=outputFile.write("\|NO")
136			if dbNamesList:
137			nl=outputFile.write("\|" + dbNamesList)
138			else:
139			nl=outputFile.write("\|")
140			appList = ""
141			if i in list(appListServersFileMatrix.keys()):
142			nl=outputFile.write("\|YES")
143			for appLine in appListServersFileMatrix[i]:
144			appLine = appLine.replace("\n","")
145			appLine = appLine.split("\|")
146			if appList:
147			appList = appList + '; ' + appLine[1]+' [AppID=%s]'%appLine[0]
148			else:
149			appList = appLine[1]+' [AppID=%s]'%appLine[0]
150			else:
151			nl=outputFile.write("\|NO")
152			if appList:
153			nl=outputFile.write("\|" + appList)
154			else:
155			nl=outputFile.write("\|")
156			nl = outputFile.write("\n")
157			outputFile.close()