diff of two files in different formats is resulting in out of both files. Need to diff two files based on usernames only [closed]

Question

#! /bin/bash
CMD="use metadata; select * from usernames; select * from personData; select usernames.SNo, usernames.DataTelid, usernames.UName, personData.ActiveInactive from usernames, personData where usernames.DataTelid=personData.DataTelid into outfile '/tmp/querydb';"
exec &> /tmp/final
cat /tmp/querydb|grep Inactive
awk -F':' '{print $1}' /etc/passwd | sort > /tmp/userlist
diff /tmp/querydb /tmp/userlist

Three files created. Querydb file has the results of the query which is a well formatted form. Userlist file has only the usernames stripped out from the /etc/passwd. This /etc/passwd is having the names of all users who login to the lab. So the Linux machine is the authenticating server.

Final file is supposed to have difference of userlist file as compared to querydb file. What is happening right now is that because it goes line by line it mentions the content from both files. I just need the final file to have the Inactive user from querydb (for which I have the cat command) but furthermore

1. if the user in userlist exists in querydb but is "active" then I don't need to mention it
2. if the user in userlist exists in querydb but is "inactive" then I need to mention it and label it as Inactive
3. if the user in userlist does not exist in querydb then I need to label it as Rogue

Answer 1

Although I am not entirely sure whether I understand you clearly, here is how I would deal with your problem:

diff file1 file2 | grep '^[<>]' | sed "s/^> \(.*\)/\1 Rogue/;s/^< \(.*\)/\1 Inactive/"

Entries missing in file1 (but present in file2) will be labeled as "Rogue", entries missing in file2 (but present in file1) will be labeled as "Inactive".